Identifying Duplicates in Excel

Submitted by User Profile

jeff on 3:13pm, Tuesday, 2004-06-22

Tech Tips

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:22pm, Tuesday, 2004-06-22, User Profile

jeff said:

Sorting means you have to select a range, click the sort button and choose which column to sort by. Autofilter means you have to select a row (not even necessary if it's the first row), then click Data > Filter > AutoFilter and then filter on the column you like. There's no recalculation when autofilting, so I don't know what you mean about that. Also, when autofiltering, you get to see all rows, not just the duplicate ones, so it doesn't hide the first row (I guess that's what you are thinking). While autofiltering has problems if you select overlapping range (where hidden rows are), a user may simply not want to resort his data, and if he does and he may want to resort back afterwards, another pain in the butt. Oh and your opinion is wrong. All your base are belong to us. Regards, Jeff P.S. Now we've shown the world how anal/stubborn we can be.

CodeDread

Post new comment