none
excel vba - how do I highlight duplicates based on 3 criteria?

    General discussion

  • Hi All,

     

    I need help big time on figuring out how to do this.  I have an excel mastersheet of about 100 names,addresses,phone numbers etc.

     

    Now when someone gives me a new excel list of 9000 names addresses, phone numbers etc, i want to compare the new list with my old list, to see how many of the 100 records in my old list appear in the new list.

    I have a macro to highlight duplicates in red, but it only works (after I sort the column) by comparing one cell with the cell above it.

    I not only want to compare the name column, but also the city column, and maybe the province/state column.  Because if I sort the list by name only, it'll give me 20 "John Smith's", but maybe they are from all different cities, and even if in the same city, they may have a different phone number and be a different person.

    How do I tell excel, "Hey excel, take that list of 100 names over there, and check to see how many of those names appear in this list? and then highlight them in red?"

    I'm hoping someone has had to do this before and can help!

    Then what I'll do, if someone figures it out, is to use the macro to read if a cell is highlighted in red, and return a 1 in the empty cell beside it.  Then I can sort all of the red cells to the top and see how many duplicate entries there are, and what they are.

    Thanks in advance for your help!

    Saturday, May 28, 2011 2:52 AM

All replies

  • I would use the dictionary feature.  Make the key the person's info like this.

    key = name & "," & address & "," & city & "," & phone number etc...

    Dim xDic as New Scripting.Dictionary

    xDic(key) = 1

    There is a method in dictionary called Exists so if you try to add a key that exists it will tell you.  You can do several different ones to let you know there are duplicates.  I use phone number first then email.  Name and address can easily be spelled differently.

    Read this post or Google it for more info

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/355cde47-3538-4d59-b53c-35179985386c/#828cfbd6-fe0b-4d86-9ce1-7a3bc2b62540

    Saturday, May 28, 2011 11:38 AM
  • Hi All,

     

    I need help big time on figuring out how to do this.  I have an excel mastersheet of about 100 names,addresses,phone numbers etc.

     

    Now when someone gives me a new excel list of 9000 names addresses, phone numbers etc, i want to compare the new list with my old list, to see how many of the 100 records in my old list appear in the new list.

    I have a macro to highlight duplicates in red, but it only works (after I sort the column) by comparing one cell with the cell above it.

    I not only want to compare the name column, but also the city column, and maybe the province/state column.  Because if I sort the list by name only, it'll give me 20 "John Smith's", but maybe they are from all different cities, and even if in the same city, they may have a different phone number and be a different person.

    How do I tell excel, "Hey excel, take that list of 100 names over there, and check to see how many of those names appear in this list? and then highlight them in red?"

    I'm hoping someone has had to do this before and can help!

    Then what I'll do, if someone figures it out, is to use the macro to read if a cell is highlighted in red, and return a 1 in the empty cell beside it.  Then I can sort all of the red cells to the top and see how many duplicate entries there are, and what they are.

    Thanks in advance for your help!


    No need for code, let Excel do the heavy lifting...though you could use code to add the formula solution to your worksheet.

    Suppose your data to look up (the long list of 9,000 rows) is Sheet1 columns A:C starting with row 2 (row 1 being a header row).

    Suppose your short list (the 100 rows) is in Sheet2 with the same layout as Sheet1.

    Then, in D2 of the Sheet2 enter the array formula =MAX(ROW(Sheet1!$A$2:$A$5)*(Sheet1!$A$2:$A$5=A2)*(Sheet1!$B$2:$B$5=B2)*(Sheet1!$C$2:$C$5=C2))

    To enter an array formula complete data entry with the CTRL+SHIFT+ENTER combination rather than just the ENTER or TAB key.  If done correctly, Excel will show the formula enclosed in curly brackets { and }

    Copy D2 as far down as you have data in A:C.  The quickest way to do this is to move the cursor over the bottom right corner of D2.  The cursor will become a bold +.  Double-click.


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Sunday, May 29, 2011 3:52 AM