How do I compare two tables with the same column definitions in different schemas?

I need to compare two lists of names

• Hello Aidan I really hope you could help me with this. I have two lists of names that I need to compare. ? Column A has 1200 names and column B has 2100. ? The names may not be exactly the same in both list. o E.g Column A could have John Smith and o Column B could have John Smith Johnson or Smith Johnson John. ? I need to find a way to compare these two columns and tell me which name is in column A, which one is in column B and which one is in ¡°A & B¡± ? After this A & B should have the same number of Names. E.g. A=1600 B=1600 Could you please help me out with this? I really need to get this done. I really appreciate your help. Maximiliano Hello Bob I really hope you could help me with this. I have two lists of names that I need to compare. ? Column A has 1200 names and column B has 2100. ? The names may not be exactly the same in both list. o E.g Column A could have John Smith and o Column B could have John Smith Johnson or Smith Johnson John. ? I need to find a way to compare these two columns and tell me which name is in column A, which one is in column B and which one is in ¡°A & B¡± ? After this A & B should have the same number of Names. E.g. A=1600 B=1600 Could you please help me out with this? I really need to get this done. I really appreciate your help. Maximiliano Hello Isaac I really hope you could help me with this. I have two lists of names that I need to compare. ? Column A has 1200 names and column B has 2100. ? The names may not be exactly the same in both list. o E.g Column A could have John Smith and o Column B could have John Smith Johnson or Smith Johnson John. ? I need to find a way to compare these two columns and tell me which name is in column A, which one is in column B and which one is in ¡°A & B¡± ? After this A & B should have the same number of Names. E.g. A=1600 B=1600 Could you please help me out with this? I really need to get this done. I really appreciate your help. Maximiliano

• Answer:

  I presume that you are looking for an exact match here =COUNTIF($B$2:$B$2100,A2) will count a2 against the entire B list =COUNTIF($A$2:$A$1200,B2) will count b2 against the entire A list I would put these two formulas in 2 blank columns and autofill down. Where both numbers are the same (should be 1) it indicates the name is present in both lists. If either column shows zero that indicates the name is not present in the specified list. IF it helps with any follow up questions you can mail me directly at [email protected] to get a list of names in A not in B, ctrl/shift/enter this formula somewhere and fill down: =INDEX($A:$A,SMALL(IF(ISNA(MATCH($A$1:$A$1200,$B$1:$B$2100,0)),ROW($1:$1200),""),ROW(A1))) to get a list of names in B not in A, ctrl/shift/enter this formula somewhere and fill down: =INDEX($B:$B,SMALL(IF(ISNA(MATCH($B$1:$B$2100,$A$1:$A$1200,0)),ROW($1:$2100),""),ROW(A1))) to get a list of names in both, ctrlshift/enter this formula somewhere and fill down: =INDEX($A:$A,SMALL(IF(ISNA(MATCH($A$1:$A$1200,$B$1:$B$2100,0)),"",ROW($1:$1200)),ROW(A1))) You will eventually see #NUM! errors which means you reached the end of your list. You can look for a lot of internet information on so-called "fuzzy matching" in excel. What you're talking about is generally referred to as that. However, much of the code that you can find and that's posted is actually super complicated and (I think) pretty difficult to implement. Let me start by asking something that may simplify our problem. First of all, are we talking about where it's on the same row????? Or just you want to know sets of data where, say, in A10 is "John Smith" and in B230 is "John Smith Johnson"? Finally, can we find something that you are satisfied to "limit" your searching of matches to - EXAMPLE - Could we see that you want to find instances like this: In any case where at least 2 "whole words" (no spaces in their middle) match. So, John Smith and John Suzie Smith, would be a match. But John Smith and Jon Suzie Smith, would not be a match. There has to be some guideline...what do you want your guideline to be? I'm confused about the whole "A&B" thing, also.. I understand basically you want to find matches, (not identical, but fuzzy)...but why 1600, why A&B, I don't get that.