finding redundant data

  • Thread starter Thread starter eternal_cat via OfficeKB.com
  • Start date 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.
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how do i parse every nth data point from a large data set in exce 1
Help! Is this possible 2
Excel Excel Show Countdown Date 7
Import Spreadsheet into Access 1
Quick/efficient way to VBA subtotal 2
Autofilter number of records 3
alternatives to embedded ifs 6
Question about copying long rows of data 2

Back
Top