+ Reply to Thread
Results 1 to 5 of 5

Removing Dupilcates formula not working anymore

  1. #1
    Registered User
    Join Date
    01-18-2011
    Location
    Ga, US
    MS-Off Ver
    Excel 2003
    Posts
    3

    Removing Dupilcates formula not working anymore

    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
    Last edited by jktyler; 01-18-2011 at 04:21 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: Removing Dupilcates formula not working anymore

    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.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-18-2011
    Location
    Ga, US
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Removing Dupilcates formula not working anymore

    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

  4. #4
    Registered User
    Join Date
    01-18-2011
    Location
    Ga, US
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Removing Dupilcates formula not working anymore

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

    Thanks so much!
    Kalin

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,088

    Re: Removing Dupilcates formula not working anymore

    "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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1