Duplicate data
in [General]
|
From: Jodi on 14 Apr 2010 13:00 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
From: Gary Brown on 14 Apr 2010 15:47 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
From: sq on 14 Apr 2010 16:31 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
|
Pages: 1 Prev: One pivot table for multiple worksheets/tabs Next: Disable Options in 2003 |