finding redundant data

  • Thread starter eternal_cat via OfficeKB.com
  • Start date
E

eternal_cat via OfficeKB.com

how can i see if i have two records in my spreadsheet that are the same? I
have over 60,000 rows of data.
 
M

Max

One way ..

Assuming the key col is col A, with data in A2 down

Use an empty col to the right, say col E
Put in E2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"x",1))
Copy E2 down to last row of data in col A

Col E will return a "1" for the 1st instances of data in col A,
"x" for any duplicates thereafter

Do a Data > Filter > Autofilter on col E
To see/filter out the duplicates,
select "x" from the autofilter droplist in E1
 
Top