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?

--
Thank you,

Jodi

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
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Jodi" wrote:

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?

--
Thank you,

Jodi

have you try advanced filter?
data-filter-advanced filter
check the unique records only box
good luck


"Jodi" wrote:

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?

--
Thank you,

Jodi