Compare two lists of data in excel

Posted on 2012-09-17
Last Modified: 2012-09-17
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.
Question by:cheesebugah
    LVL 23

    Expert Comment

    Are the names in 2 separate columns?
    LVL 4

    Accepted Solution

    You can try something like:


    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.
    LVL 11

    Expert Comment


    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.
    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:


    copied down.. where Sheet2 is name of other sheet

    then in Sheet3,


    copied down and to next column.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    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…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now