Duplicate data

  • Thread starter Thread starter Jodi
  • Start date Start date
J

Jodi

I have a file that contains duplicate supplier records and I want to be able
to identify where a supplier number is duplicated and then delete all but 1
of the duplicate records. I was given a COUNTIF formula but it is not
working.

I was told to add 2 columns and in the first blank column type =Row() in the
cell. In the second blank column type =IF(COUNTIF(A$1:$A1,A1)>1,0,1)

My supplier numbers start in Column A Row 2. How can I get this to work?
 
I would...
- Sort on supplier number
- in the first available column (in row 2), put the formula...
=Row()
- in the next available column (in row 2), put the formula...
=if(A1=A2,1,0)
- copy these formulas down the length of the data
- make the formulas into values using the PASTE SPECIAL >Value funcion
- Sort on the 'formula' column that has 0's and 1's
- Delete all rows that have a '1' in the 'formula' column
- Re-sort however you desire
- to re-sort into the original order, sort on the 'Row' column
- Delete the 2 helper columns
 
have you try advanced filter?
data->filter->advanced filter
check the unique records only box
good luck
 
Back
Top