+ Reply to Thread
Results 1 to 8 of 8

Finding Duplicates across Sheets without Exact Values

  1. #1
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Finding Duplicates across Sheets without Exact Values

    Hello all,

    Apologies if I have duplicated a topic already posted but my problem seems to be a little different than the ones I've seen here.

    I have two address lists that are formatted differently. One list, 'Symphony,' has names combined (Mr. and Mrs. John Smith) and the second list, 'Library,' has first and last name fields. I need to search the Symphony list and highlight any combined names that might have "Smith" in them. I don't want it to automatically delete these fields because there may be more than one person with the last name Smith.

    So, essentially, what I would like to do is search each cell in the 'Symphony' Combined Name column and see if it has any word that is contained in the 'Library' Last Name column. Then I would like it to highlight that cell. If the highlight is not possible then I would even take my chances with just deleting them at this point.

    Thank you so much for any advice you could give.

  2. #2
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Finding Duplicates across Sheets without Exact Values

    Assuming that Library and Symphony are different sheets and that Last names are in column B:
    I would just put a formula in the column next to names that says:
    =IF(COUNTIF('Library'!B:B,B1)>0,B1,"")
    Then drag the formula down as far as the list goes.

    This will make it so that all names that are in the Library list will be displayed next to their name on the Symphony list. You can then create conditional formatting to make it so that any non-blank cell in the column with the formula is highlighted.

  3. #3
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Finding Duplicates across Sheets without Exact Values

    This didn't do it. I have identified someone who is on both lists, we will call her Dr. Lilly White.

    On the Symphony sheet she is listed in one column as "Dr. Lilly White".

    On the Library sheet she is listed in three columns: Greeting (Dr.), First Name (Lilly), and Last Name (White).

    When I search the Symphony sheet's one column against the last name column it didn't give me anything by Dr. White's name.

    I'd also like to add that I tried breaking text to columns but the columns are all spread out because some names have more things than others ("Dr. Lilly White" is broken into 3 columns vs. "Dr. and Mr. Lilly White" which is broken across 5). It's just a mess.
    Last edited by twain101; 03-15-2012 at 04:35 PM. Reason: clarification

  4. #4
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Finding Duplicates across Sheets without Exact Values

    Ouch. It's going to be hard if the naming conventions aren't consistent across both sheets. Is it possible to create 1 column on Library that would combine the values in A, B and C and then search that column?

    If Column A is Greeting, Column B is First Name, Column C is last name, in column D, you could write the following:
    =A2&" "&B2&" "&C2, which would return: Dr. Lilly White.

  5. #5
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Finding Duplicates across Sheets without Exact Values

    So now I have separated out the Last names from the Combined name list in the symphony sheet. I now how a 'Symphony' Last Name column and a 'Library' Last name column. How can I make it highlight and or delete the rows that exist in both columns?

  6. #6
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Finding Duplicates across Sheets without Exact Values

    In order to delete them, you'll have to run a macro

    I suggest that you do the same formula as before, but instead of having it display the name, have it display "x".
    So, assuming that last names are in column C on both sheets, in column D on the Symphony sheet, write this formula.
    =IF(COUNTIF('Library'!C:C,C2)>0,"x","")

    You can then make this macro to delete the rows with "x"s in column D in VBA (Alt + F11)


    Please Login or Register  to view this content.
    I suggest testing this out on a copy just to make sure it's what you're looking for.

  7. #7
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Riverside, CA
    MS-Off Ver
    Excel 2010
    Posts
    132

    Re: Finding Duplicates across Sheets without Exact Values

    This might be alittle work, but here goes:

    In 'Library' create a column D, E, F next to your last name column (assuming last name is in Column C).

    in E2 paste the last name you are looking up.

    Enter the following in column D and paste down:
    Please Login or Register  to view this content.
    In F2 enter:
    Please Login or Register  to view this content.
    Now select column D and set a conditional format to highlight the cell when it = 1.

    when F2 shows more than or equal to 2, then you know there may be a duplicate.

    Then to make it easier to pull up the "duplicates", put a filter on column D and have it pull up values that equal 1.

    Sorry for all the work, but one of the geniuses could probably do that in one single code or macro.
    Last edited by haru; 03-15-2012 at 08:25 PM.
    Happy Excel'ing!

  8. #8
    Registered User
    Join Date
    03-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Finding Duplicates across Sheets without Exact Values

    SO...This is what I ended up doing:

    In order to get all of the names separated, I split them into columns and sorted A-Z started with the right-most column. Highlighted everything but the last name, deleted and allowed cells to shift to the left. This took a while but then I had my column of last names and it was much easier to use the methods described.

    Thanks guys for talking me though this. This was one of those cases where there just wasn't a simple fix.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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