Show only rows with duplicate values in a column?

[Mechphisto]

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

 

[Lars-Åke Aspelin]

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

 

[BRO]

i am not sure i`m understanding ur problem very well, however we`ll
try giving it a shot.

For the duplicates issue, and after u sort (or b4, wont realy matter),
try going to the leftmost empty column, and use the countif function

so, assuming ur table range is from A1 to E100, and that ur looking
for duplicates in the last name (which is Column B) for example, type
the following in F1

=countif($B$1:$B$100,B1)>1

then drag (copy & Paste) the formula to cell F100

Now u`ll have (in column F) "TRUE" for duplicate names, and "FALSE"
for non-duplicated amounts.

then you can filter ur table (Data --> Filter --> Autofilter) --
Please make sure to use the filter on the column headings row (Name,
Last name, etc..)

Please inform me if it works or not.

 

[BRO]

YES u can have duplicate data in two coumns sorted out,

repeat the same procedure, in column G and then filter for "TRUE" in
column F, and for "TRUE" in column G.

 

[Lars-Åke Aspelin]

YES u can have duplicate data in two coumns sorted out,

repeat the same procedure, in column G and then filter for "TRUE" in
column F, and for "TRUE" in column G.

To filter for TRUE in F and TRUE in G could give to many hits, see
example below

John Young TRUE TRUE
John Smith TRUE TRUE
James Brown TRUE FALSE
James Young TRUE TRUE
John Smith TRUE TRUE

In this example only the two "John Smith" should be displayed I guess.
John Young and James Young are not duplicates. Right?

Lars-Åke