For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar

Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22*am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. *You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. *Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar

Thanks Pete. The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar


"Pete_UK" wrote:

Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar

You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55*pm, Templar wrote:
Thanks Pete. *The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. *There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar



"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. *You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. *Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -

OK. Let's see here. I have column A with all the names in it. I have
column B to which I've applied your formula effectively and therefore have
blanks in which redundant names were located. I highlighted Column B and
applied "Autofilter".
Nothing happened. I then looked for the word "Blanks" in a pull down menu
but could not find the word "Blank" or, therefore, select it.

What do you thing?
--
Templar


"Pete_UK" wrote:

You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar wrote:
Thanks Pete. The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar



"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -

In B1 you should have a downward pointing arrow, and if you click on
that you will get the filter choices:

(All)
(Top 10...)
(Custom...)
....
your names
....
(Blanks)
(Non-blanks)

You might need to scroll down the list to get to Blanks if you have a
lots of names, but then you just click on it. Only the rows which have
blanks in that column will be displayed (and the colour of the row
identifiers will change - if you look at these you will notice that
some are missing, as they have been filtered out, or temporarily
hidden). Then you can follow the other instructions.

Hope this helps.

Pete


On Oct 3, 4:47*pm, Templar wrote:
OK. *Let's see here. *I have column A with all the names in it. *I have
column B to which I've applied your formula effectively and therefore have
blanks in which redundant names were located. *I highlighted Column B and
applied "Autofilter".
Nothing happened. *I then looked for the word "Blanks" in a pull down menu
but could not find the word "Blank" or, therefore, select it. *

What do you thing?
--
Templar



"Pete_UK" wrote:
You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar wrote:
Thanks Pete. *The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. *There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar

"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. *You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. *Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

DataFilterAdvanced Filter.

Check "Unique records only" and "Copy to another location".

Select a cell below the data range to copy to.

Delete the original data after the copy to is successful.


Gord Dibben MS Excel MVP

On Fri, 3 Oct 2008 03:22:03 -0700, Templar
wrote:

For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.