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]

I don't believe that I have MSQuery

I am using the =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) from JefferyBrown

The source is a csv file of some of the data that we pulled from my vendor's website using PHP code.

 

[Jonmo1]

Hmm, it imports them as Text.

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

 

[Jonmo1]

I don't believe that I have MSQuery

I am using the =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) from JefferyBrown

The source is a csv file of some of the data that we pulled from my vendor's website using PHP code.

If you have XL2007 you should have Query...

On the Data tab
From Other Sources
MS Query
Excel Files
Browse to and select the file (even if it's the same file you're working in)
You may have to select "System Tables"
Find and select the sheet and header
Click next a few times and you're done.

 

[shg]

Remove Duplicates seems somehow hostile to mixed text and numbers.

OTOH, I made 500 random numbers 100 to 999, 1000 random 2-character strings AA to ZZ, randomized the order, and removed duplicates: Perfect.

 

[jerem]

Remove Duplicates seems somehow hostile to mixed text and numbers.

OTOH, I made 500 random numbers 100 to 999, 1000 random 2-character strings AA to ZZ, randomized the order, and removed duplicates: Perfect.

Anything new on this subject? I'm running into the same issue. Tried everything to check if duplicates are real duplicates and they are. Yet Excel doesn't recognize them. Data is alphanumerical.

 

[jeffreybrown]

Hi jerem,

Just a thought...since this thread is over a year old and to maximise visibility, might be a good idea to start your own thread...

 

[jerem]

Hi jerem,

Just a thought...since this thread is over a year old and to maximise visibility, might be a good idea to start your own thread...

Hi Jeff. You're right. I just didn't want to create duplicates. In the meantime I decided to go for autofilter and unique record.

 

[jeffreybrown]

That is a very good option...I use it all the time

You really wouldn't be creating a duplicate, the best reason is because at over 25 posts not many people will pop in to see what is going on

 

[grafiti4u]

I was having this problem too, and I figured out something that worked. Even though i didn't see blank spaces, they were there. I installed AbleBits.com awhile back (a little add in that works within Excel - pretty sure it was free too!). I clicked it, and it searched my entire worksheet and it found and trimmed tons of blank spaces. Once this was done, my duplicates finally would highlight. Hope this helps!

 

[mziaei110]

It's a bug when you have a mix of texts and numbers.
Sort your data first, then remove duplicates