Excel: How to find duplicate records and delete them? 1

[jbit01]

I've found several scripts that delete duplicate instances of a record and leave the original behind, but I need a script to find all records in a worksheet that have dups and delete them both.

Example...
TN430
TN300HL
TN300HL
TN250
TN250

I need the code to delete both lines containing TN300HL & TN250 while leaving the record that doesn't have a duplicates alone.

Anyone come across this before???

 

[jdhilljr]

SORT BY THE COLUMN THEN INSERT A BLANK ROW A
IN A2 PUT =IF(B2=B1,1,0)
THEN COPY DOWN
SELECT ENTIRE ROW A AND PASTE SPECIAL "VALUES"
THEN DATA SORT BY A
AND DELETE ALL WITH 1 IN ROW A

THIS SHOULD REMOVE ALL DUPES.

JIM

 

[KenWright]

Select the data and do Data / Filter / Advanced Filter / Tick the 'Unique entries only' option, and check the 'copy to another location' option. Choose the other location and hit OK. Now just replace your original list with the new list.

Regards
Ken..................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[jdhilljr]

Ken,
The only problem I found with using the filter is if there is any change no matter how small it counts it as a unique record. Is there a way to only compare the value of the cell with this option. If so I will give you a star.

Jim

 

[KenWright]

Not quite sure what you mean, though you are quite right in that the smallest difference will cause a record to be unique from any other; can you give me an example of what you would want to avoid?

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[jbit01]

Thanks for the input guys...

KenWright,
I’ve tried accomplishing this with the advanced filter but it doesn't work. I used the settings "Copy to another location" and "unique records only" but all it does is filter out one of the dups. It doesn't eliminate both the original and the dup. I’ve tried testing it a dozen different ways and no success.

jdhilljr,
I've also tried using the IF statement and the results are the same as the adv. filter. It flags the dup with a "0" but flags the original with a "1" thus leaving the original behind.

This one is pissing me off because I know there is a way to do it through VB but I have limited VB experience and it doesn't seem possible to do it through the GUI.

Thanks again!

 

[bluedragon2]

Do jd's except use the following formula:

=IF(OR(B2=B1,B2=B3),1,0)



Blue

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

Ahhhhh - Just twigged that you actually said delete both dupes, so that all you want is literally any number that didn't have a duplicate number in the original list.

With your data in Col A in A1:A1000, in B1 put the following and copy down:-

=COUNTIF($A$1:$A$100,A1)>1

Select A1:B1000 and do data / Filter / Autofilter

Click the dropdown on B1 and select TRUE

Select all the data you can see and do Edit / Go To Visible cells only, then do Edit / Delete Entire Row.

Now click on your dropdown in B1 and select ALL.

I think that should be what you want.

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[mscallisto]

B1 formula is =COUNTIF(A:A,INDEX(A1,1,1))
Drag formula to last row in Col A to get the following results:

Col A Col B
TN430 1
TN300H 2
TN300H 2
TN250 2
TN250 2


Sort Col B
Delete everything in col B not = 1

This also works when Col A is in any sequence


Hope this helps (Not sure I totally understand problem)

 

[jbit01]

KenWright, that was exactly what I was looking for, thanks. I modified it a bit and it works great. The only thing I’m trying to figure out is when trying to sort the results, I think Excel is seeing the formula inside the cell instead of the "True" or "False" values and therefore will not sort.

 

[KenWright]

Why would you want to sort them??? If you are looking to delete them then use the method I gave you which will do it all in situ.

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[jbit01]

Go it, i was consumed by getting the function to work. thanks a million!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1

 

[KenWright]

Good stuff - Glad you got sorted.

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[Adnane]

Hi,

another way of getting rid of the duplicates would be to use a pivot table. it will give the distinct list of your values. all what you need to do is to build your table in such a way that at the end it will look like your original table. then copy and paste it in a new worksheet.

hope this helps

Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica