Identifying Duplicates in Excel
Let's say you have a very large Excel spreadsheet and you want to identify all the rows that have duplicate values in a particular column. For this example we will assume that the 'A' column has the values you want to look at. We also assume the data starts on row 2 and goes to row 1000.
- insert a column ('B')
- in B2 put the following formula: =countif(A$2:A$1000, a2)>1
- copy the contents of B2 to the range B3:B1000
- now select row 1 and turn on auto-filter (Data > Filter > AutoFilter)
- finally, filter in the B column for all TRUE values
The listed rows are the ones that have duplicate values in the 'A' column.
Variations on this tip will follow I'm sure ;)
Regards,
Jeff
At 6:42pm, Tuesday, 2004-06-22, 
Rob said:
Rob said:
I'd use the formula '=(A2==A1)' and the rest of the instructions could stay the same.
If you want to delete the duplicates, you might have trouble in older versions of Excel because of the way autofilter works. To get the same result without autofilter, try this (with the same assumptions as the original post):
- insert a column ('B')
- in B2 put either formula: [code:1]=countif(A$2:A$1000, a2)>1[/code:1] or [code:1]=(A2==A1)[/code:1]
- copy the contents of B2 to the range B3:B1000
- now select column B2 (right click the 'B' at the top), copy it
- right click the column heading (the 'B' at the top) and pick "paste special"
- on the "paste special" dialog, pick "values" and press OK
- sort by column B and all the rows that say 'TRUE' have a duplicate in column A
The advantage here is that you can pick all the rows that have duplicates (TRUE) in the list and delete them without the formula being updated. In the original, the formula will change when you delete a duplicate. Really, either way could be seen as an advantage depending on the data you're working with.
Post new comment