Error in Marking Duplicate entries

[Jeanie]

I have a list of records - about 5,000. Each record has 4 fields. The list of 5,000 is not unique, but I want to make it unique. I can't just filter the list because I need to review each record and see which one to delete (there are 2 fields that could be different on each record). The only way I know how to do this (because I'm Excel illiterate) is to mark the duplicate ID numbers and then remove them after reviewing them. This would be easy and not too big of a deal because there should only be about 30 or so duplicates. However, I am running into problems.

The field I am trying to mark as duplicates is 17 digits long and is a number stored as a text. When I use conditional formating with this formula +IF(COUNTIF(RANGE1, A5)>1,TRUE,FALSE) it marks records as duplicates that are not duplicates. I have tried to tag the records as duplicates with a similar formula, but again, it marks some as duplites that are not and does not mark some that are.

Is there a better way for me to do this?

Thanks,

Jeanie

 

[firefytr]

Try this as your CF instead ...

=COUNTIF(RANGE1,0+A5)>1

If that does not help, try posting some examples of your data and show some that would be duplicates and some that would not.

-----------
Regards,
Zack Barresse

 

[Jeanie]

Thanks for the suggestion, Zack. Unfortunatelly it did not work. Here is a sample of my data:

Recip ID Dt of Svc Prov ID Clm ICN Emergent?
333333333 11/03/2003 515 00331711355001800 Emergent
333333333 12/06/2003 515 00335011353001700 NonEmergent
333333333 08/03/2004 515 00422411359000300 NonEmergent
333333333 08/03/2004 515 00422411359000300 Emergent
222222222 02/02/2005 515 00504611355004900 NonEmergent
222222222 04/11/2005 515 00510911356005000 Emergent
222222222 04/11/2005 515 00510911356005000 NonEmergent
111111111 02/21/2005 414 00511911809100100 Emergent
222222222 09/06/2003 515 20325900252000235 Emergent
111111111 07/06/2004 414 20420400254000275 NonEmergent
111111111 02/21/2005 414 20505900254000542 Emergent
111111111 02/21/2005 414 20505900254000543 Emergent
333333333 01/20/2005 511 20510900254000047 NonEmergent
222222222 09/06/2003 515 40407200802100226 Emergent
333333333 11/03/2003 516 40407200802100235 Emergent
333333333 12/06/2003 516 40407200802100236 NonEmergent
222222222 09/06/2003 515 40407200802200226 Emergent
333333333 11/03/2003 516 40407200802200235 Emergent
333333333 12/06/2003 516 40407200802200236 NonEmergent

In this example, the bottom six all formatted as duplicates when they are not.

Thanks for your help!

Jeanie

 

[xlhelp]

Hi, Jeanie.

Try highlighting the whole clm ICN column. Go to Data, Text to columns,click finish and then try your formula.

This process converts your text to numbers


Member AAA - Abolish Abused Abbreviations

 

[Jeanie]

I tried that - it still does the same thing - marking duplicates where they do not exist. Thanks for the suggestion, though.

 

[xlhelp]

I am willing to take a look later tonight if you wish to send your file. xlwrdhlpATyahooDOTca

Member AAA - Abolish Abused Abbreviations

 

[Jeanie]

Thank you - I will forward to you.

Jeanie

 

[firefytr]

You can add a helper column (I'm assuming A is your data, I'll assume you've put this in E2 and copied down as needed)...

=A2&B2&C2&D2

Then in your conditional formatting selecting A enter (Formula Is, assuming you have A1 as the activecell)...

=(ROW(A1)<>1)*($A1&$B1&$C1&$D1<>$E1)

-----------
Regards,
Zack Barresse