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 7:11pm, Tuesday, 2004-06-22, 
Rob said:
Rob said:
It's not circular, when you copy =(B2==B1) down... ooohh. Yeah, should've been =(A2==A1) but in column B. I'll edit my original.
The sorted values vs autofilter thing isn't [i]that[/i] clear cut. If it's a complex worksheet, the recalculation could take a while. Or the user might need to read each row and decide what to do with it, not just delete all duplicates. More importantly, autofilter hides rows and if you delete them (at least in old versions of Excel) it leaves blank rows. Even in Excel 2000, if I delete some autofiltered rows then try to undo, I can't get them back. Sorting doesn't cause these problems. Besides, a sort only takes one button click, autofilter takes a few.
Post new comment