Show only rows with duplicate values in a column?
 

I've a spreadsheet with thousands of rows of people (last name, first
name, address, etc.)
I need to weed out duplicates, and sorting by lastname, firstname and
then scanning the firstnames for duplicates that have the same last
name is onerous!!

Is there a way to have Excel (2003) show only rows in which there are
more than one of a given value in a column?
So let's say there's only one row with a value of "Jones" in the
lastname, it'll now show that row but it'll show the three rows with
"Smith" in the column?

(Would be really cool if it showed rows in which there are more than
one with the same value in TWO columns, but I won't dream that big.)

Thanks for any suggestions!
Liam

Show only rows with duplicate values in a column?
 

On Wed, 1 Jul 2009 07:00:42 -0700 (PDT), Mechphisto
wrote:

I've a spreadsheet with thousands of rows of people (last name, first
name, address, etc.)
I need to weed out duplicates, and sorting by lastname, firstname and
then scanning the firstnames for duplicates that have the same last
name is onerous!!

Is there a way to have Excel (2003) show only rows in which there are
more than one of a given value in a column?
So let's say there's only one row with a value of "Jones" in the
lastname, it'll now show that row but it'll show the three rows with
"Smith" in the column?

(Would be really cool if it showed rows in which there are more than
one with the same value in TWO columns, but I won't dream that big.)

Thanks for any suggestions!
Liam

If your data is in column A and B from row 1 to row 100, you can
introduce a helper column C and put the following formula in C1:
=A1&B1

(this could be expanded to combine as many columns you want, but this
example just takes two columns)

Copy cell C1 down as far as you have data in columns A and B, to row
100 in this example.

In cell D1 you put the following formula:

=IF(SUMPRODUCT(--(C$1:C$100=C2))1,"DUPLICATE","")

change the 100 to suit the number of data row that you have.

Copy cell D1 down to D100.

You can now apply an autofilter on column D to only display the rows
with DUPLICATE.

Hope this helps / Lars-Åke