Software

Ask something
Discussion Locked

Delete Duplicate Entries in Excel?

0 helpful
7 replies

by borginva

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a sheet with hundreds of customer names. Dozens are duplicates. Other than sorting and reading each entry one by one, is there another way to clean duplicate entries in Excel?

7 replies

by ayoshi

What I've done in situations like this, is to sort the data, then set a new column with the following formula:

=IF(B12=B13,"1","")

Then a "1" will appear in that column for each duplicate entry. You can then simply go and delete the lines that have a "1" in that column.

by borginva

That did help me, but I have another question. Can you give me a formula that will clean out dups in one column but not delete dups in A if column B has a different value:
bill cas
bill vm
sue cas
sue cas
sue vm

So when I do it,I keep the two Bills but get rid of one Sue/CAS.

by hinrgman

You did not state what version of excel you are using. If you are using office 2000 or 2002 try data, filter, advanced filter, then check the unique records only box and check copy to a new location and it will give you a list without duplicate records.

by borginva

I am using Excel 2000 SP2. I do as you said, but when I am playing with the criteria, I keep getting invalid entries. I am using cells a-h 1-884. When I do the steps you gave me, it comes up with $A$1:$H$874 in the list range field.
What ma I doingwrong? I put in the criteria range the same as what is defaulted in the list range box, but it does not work.

by borginva

Ahhhhhhhhhhh...........

Using the codes from AYoshi after I concatenate some fields, it works great!

Thanks.

by borginva

AYoshi, this is my first time using TechRepublic and I thought I could still give you those points. If there is a way, please let me know. I am sorry I messed up.

This discussion is closed to new replies.