• Status: Solved
• Priority: Medium
• Security: Public
• Views: 558

# Compare two lists of data in excel

I am trying to compare two lists of data in excel and give me the data that is not in the  Sheet 2

Sheet 1
First Name and Last Name
John Smith
Mary Jane
Bob Alan

Sheet 2
First Name and Last Name
Mary Jane
John Smith

Sheet 3
First Name and Last Name
Bob Alan

I am using excel 2010.
0
cheesebugah
• 2
1 Solution

Commented:
Are the names in 2 separate columns?
0

Commented:
You can try something like:

``````=IF(ISNA(VLOOKUP(Sheet1!A2,Sheet2!\$A\$2:\$A\$65535,1,FALSE)),Sheet1!A2,"")
``````

Pasted into Sheet3 and copied down for as many rows are in Sheet1.  It will check starting with Sheet1 Cell A2 in the first cell and look for that value in column A, excluding the header cell.  If it is not found it will print the value in the cell, or else it will leave the cell blank.
0

Commented:
=IFERROR(MATCH(A3,C:C,0),A3)

Where they are both vertical lists, this would either provide the row in the second list (column C)  where the item in cell A3 is found, or it would list the name in A3.
0

Commented:
I will assume that you have the first name and last name in separate columns...

So in Sheet1, in a new column, say at C2 (assuming data in columns A:B), add formula:

=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2),COUNT(C\$1:C1)+1,"")

copied down.. where Sheet2 is name of other sheet

then in Sheet3,

=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS(\$A\$2:\$A2),Sheet1!\$C:\$C,0)),"")

copied down and to next column.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.