remove duplicates 2007 doesn't work

[mljohn]

I have one column of part numbers that contains duplicates.

I conditionally format the column to show duplicates in red.

Then I click on the "Remove Duplicates" in the DATA tab and it says that the duplicates have been removed but I can see that they have not.

Why?

How do I remove the duplicates?

Matt

 

[shg]

Maybe you have spurious leading or trailing spaces?

 

[jeffreybrown]

Sounds like you have done it right.

Are you sure the duplicates really are or could there be some blank spaces around those duplicates?

Give it a quick test.

Let's say you think A6 and A20 are duplicates and one should have been removed.

In a blank cell use a formula such as =A6=A20. If they equal each other it should return TRUE.

 

[mljohn]

I ran your formula =A1=A2 and it said "True".
I ran the "Remove duplicates" and it said that there are no duplicates to remove even though the "Conditional Formatting (red)" and my own eyes tell me differently.

Any help?

 

[jeffreybrown]

Not sure why that method won't work for you.

Let's try some code.

Sub test()
    Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("A1:A" & LR).RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

If it's not column A adjust the range and if no header change to xlNo

 

[mljohn]

Solved???

I noticed that instead of selecting the column and then running "Remove Duplicates" that if I selected the cells that were duplicates, in red, that it worked better, but not perfectly.

So...

I applied "Conditional Formatting to have any duplicates show in red.

I then sorted the red to the top by sorting by cell color.

I then selected the red duplicates only and then ran "Remove Duplicates and it worked and it removed some of the duplicates.

So I selected some of the duplicates and it removed them.

Then I selected some more and then it removed them until I got them all.

This seems more than I should have to do.

It almost seems like my computer is not strong enough but I have 8 GB of ram with a 64 bit processor.

Any suggestions?

Matt

 

[jeffreybrown]

Hi Matt,

I wouldn't think it is your computer, but don't quite know what it could be. Just ran a test on ~ 300k records and it removed all duplicates in a matter of seconds.

Did you try with the code? Curios if it would make a difference?

Could something be fishy about the workbook you are working with? Did you try in another workbook?

Open a new workbook and type a header in A1 then A2 Jan. Copy this down as many rows as you want to test and then run Data tab >> Remove Duplicates >> My data has headers >> Column A checked. After running Remove Duplicates you should be left with 12 records.

What happens with this test?

If nothing good happens here you could attach a sample workbook to a site like Box.net and provide the link here and I could take a look.

 

[Jonmo1]

Works for me as well.

It may depend on your definition of Duplicate.
Can you post the conditional formatting formula that shows duplicates?

When you click Remove Duplicates,
Have you highlighted only a single column, or multiple?
It would then give you a window to put checkmarks on which column to consider in the duplicate query.
You should only have checks on the column that contains the duplicates.

Also put a check on "my data has headers" if appropriate.

 

[mljohn]

JefferyBrown

I tried your code and it beeped and just sat there. Nothing was removed.

I tried the =A6=A7 and got "True"

I tried the new excel file and ran the months to 17000 rows and removed duplicates and it worked fine.

I deleted my data and special pasted, values only, the data into a new excel file sheet and tried the remove duplicates and it said "16102 duplicate values found and removed; 1507 unique values remain."

Which is the number before I removed the duplicates.

When I remove the duplicates from the file I copied from it just says "No duplicate values found"

When I double click on the cells that have duplicates I see that there are no spaces before or back and no apostrophes etc.

Help!!!


Matt

 

[jeffreybrown]

Hi Matt,

Unless Jonmo or shg have an idea I'm at a lost.

Can you post the workbook to Box.net?