Latest Lists

In Excel how do I find duplicate names in two different lists?

I have two different long name lists. And there are a lot of names appearing in both lists. How do I find those names and highlight them? I have these names in two different columns as my lists. I used Edit --> Search but can search one name at a time. It will takes me a year to type in all these names. Is there any way I can "compare" these two lists?

Public Comments

1. open them and make a search
2. There's a couple of ways to do this. I usually copy both lists into a new spreadsheet, and then sort them in ascending order. Now you've got 1 list, sorted alphabetically. From there, I insert a column next to the names and put in an "if" statement that compares the name on the same row I'm writing my "if" statement on to the name above. If it matches, give it a 1, if it doesn't, give it a 0. Then, sort your column with the "ifs" in ascending order, and you have all of your duplicates pulled to the top. An easier way would be to use a pivot table, as long as you have more than just a list of names for data. Hope this helps.
3. You can do this without sorting if you use the MATCH function. Assuming your data is in column A and in Column B, the following formular can be pasted into column C starting with cell C1. Then simply copy down to as many cells as the list is long. Here's the formula: =IF(ISERROR (MATCH(B1, A:A, 0)), "No Match", "Match") Your results wil be "Match" or "No Match" accordingly. You can then set conditional formatting to recognize the result and change color, font, border etc as desired. Regards, QwertyKPH