Removing Dupilcates formula not working anymore

**jktyler** · 01-18-2011, 02:27 AM

I'm using Excel 2003 and I've been using this formula to sort out duplicates:

=if((b2=b1)*(c2=c1),"x","") - which will sort first name, last name columns

and

=if(b2=b1,"x","") - which will just do a single column.

I just arrange Z-A and all the dupes come to the top, which I can delete from my main dupecheck list, and then copy the dupes to the list I'm working on and weed them out the same way.

This may or may not be the source of the problem, but when I started working in GoogleDocs, the formula stopped catching dupes. Not all of them, just some of them. One of the people that I'm working with uses OpenOffice, so these names get pasted from OpenOffice, to GoogleDocs, then I download them, or just copy/paste them, into my Excel sheet. I don't see any pattern as to when certain names aren't working anymore.

I'm assuming that there's some formatting that makes them not appear the same to Excel, even though to my eye they look the same. I can't see any difference, and tried to remove all formatting and it still doesn't help. I've also tried using data>filter>advanced filter, but they still don't pick them up.

One odd thing I have noticed is that the first row of data (B column), which is usually last names, does work, but the second row of data (C column) is what is messed up. Basically =if(c2=c1,"x","") doesn't find the dupes. I can run =if(b2=b1,"x","") and then visually scan for dupes and see if the first name is the same, but man what a pain for a 5k list.

It would make more sense to me if this happened for all dupes, but its just a few here and there. No idea why its doing it, how to fix it, or if there's a better way to find duplicates in my situation.

Any ideas?

Thanks,
Kalin

**TMS** · 01-18-2011, 03:02 AM

Press F2 on one of the cells that should show as a duplicate but doesn't. Check to see if there are any trailing spaces or non-display characters.

You might be able to use TRIM or CLEAN to remove them, if they are present.

Regards.

**jktyler** · 01-18-2011, 04:17 PM

Ah! Yes, there is a space after the name. How did that get there? I've never used TRIM or CLEAN before. Will I be able to do this for the entire list at once? Is there a way to be sure it doesn't happen again? Maybe something I can avoid? Or will I have to run CLEAN or TRIM on each list I add to the dupecheck? Really appreciate your help. This issue has been very problematic for me, increasingly so, for a while now.

Thanks!
Kalin

**jktyler** · 01-18-2011, 04:21 PM

CLEAN didn't work, but TRIM seems to have removed the idiot space :D

Thanks so much!
Kalin

**TMS** · 01-18-2011, 04:27 PM

"How did that get there?" Well, it's your data and you're importing it so I guess I'd have to direct that question back to you ;-)

You could try changing your formula to:

=IF(TRIM(CLEAN(C2))=TRIM(CLEAN(C1)),"x","") and dragging down (or just replacing the original formulae).

Regards