[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

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
Asked:
cheesebugah
  • 2
1 Solution
 
NBVCCommented:
Are the names in 2 separate columns?
0
 
DustinKikuchiCommented:
You can try something like:

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

Open in new window


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
 
ScriptAddictCommented:
=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
 
NBVCCommented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now