Solved

# Compare two lists of data in excel

Posted on 2012-09-17
537 Views
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
Question by:cheesebugah

LVL 23

Expert Comment

Are the names in 2 separate columns?
0

LVL 4

Accepted Solution

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

LVL 11

Expert Comment

=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

LVL 23

Expert Comment

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

## Featured Post

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…