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

 

[mljohn]

Here is the file at Box.net
http://www.box.net/shared/f875382oh0

 

[shg]

That's very curious. I did it four ways and got these different results:

<TABLE style="WIDTH: 166pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=221><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3299" width=93><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 70pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=93> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Unsorted</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Sorted</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Header</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1449</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1383</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>No Header</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1449</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1368</TD></TR></TBODY></TABLE>

The lower-right one has no duplicates.

 

[jeffreybrown]

Thanks shg,

I too got the same results, but then I ran

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

from ron de bruin's site and got 1368 (no duplicates like you)

 

[mljohn]

Thanks guys

JefferyBrown your formula seems to work but it is very frustrating that I don't know why the remove duplicates has not been reliable.

Would you please explain your formula?
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

Matt

 

[Jonmo1]

This is bizzarre!!

I confirm same thing happens in 2010.

And, The Advanced Filter - Unique Records recognizes them as duplicates as well.
But the Remove Duplicates function does not remove them.

 

[shg]

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

There are none of those characters in there. =TEXT(A1, "@") has the same effect.

Very curious.

I confirm same thing happens in 2010.

That's disappointing.

 

[Jonmo1]

The plot thickens....

I imported the data to a new book using MSQuery.

I was then able to remove duplicates successfully.

 

[mljohn]

I was just hoping to fix my data not create a mystery novel.



However I cannot wait to see what happens at the end of the novel.

Who dunnit?

 

[Jonmo1]

Well, per my last post...

Import that data using MSQuery
You don't even have to do it to a new book, a new sheet is fine.
You can remove the duplcates from there.


Where is your data coming from?
Problem's gotta be at the source.

 

[shg]

jonmo, does the query convert numbers to text, or leave them as numbers?