+ Reply to Thread
Results 1 to 6 of 6

Compare two sheets to find matches?

  1. #1
    Registered User
    Join Date
    05-03-2012
    Location
    Plainsboro, NJ
    MS-Off Ver
    2010
    Posts
    20

    Compare two sheets to find matches?

    I know this is simple, as usual I cannot figure it out and my search here didn't return helpful answers (prob. using wrong terminology).

    I am working in Excel 2010. I have two spreadsheets. One column on each has matching information, but all other columns are different on each. I am trying to compare the two tables and, based on the matching data, mark off the rows that match on both; all the rest of the rows I don't care about). I need Excel to search both sheets and, if there are matches, return simply an X in a blank column on Report 1. Here's a sample:


    REPORT 1:
    A B C D E <--------this is the column where I assume the formula must go
    Name Initials Job Title Home City where I want the check-offs to show
    1 John Smith JBS Sales Manager Chicago X
    2 Mike Jones MLJ HR Director Miami
    3 Susan Drew SDD Accounting Clerk New York X

    REPORT 2:
    A B C D
    1 Territory Phone # Initials Hire Date
    2 Southeast 999-123-4567 SDD June 1999
    3 Pacific NW 999-987-6543 BIW April 2001
    New England 999-102-8374 JBS January 1984


    [It goes without saying that the two spreadsheets contain hundreds of rows and columns, I don't want to have to manually look down the two Initials columns to find matches.]

    I hope I've explained it clearly. If possible, can you not only explain but provide the formula itself that I can copy-paste.

    As I said above, I think this is simple and I will feel dumb for having had to ask.

    Thanks,
    Cindi

  2. #2
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Compare two sheets to find matches?

    Hi -

    You can use the MATCH function to do this for you, along with an IF statement and the ISERROR function as follows:

    =IF(ISERROR(MATCH(B2,$C$2:$C$4,0)),"","X")

    Where B2 will be the initials (JBS in the first line according to your example) and $C$2:$C$4 is the range of cells in report 2 that contains the list you want to search. If there is not a match, then the formula returns a blank "". If there is a match, the formula returns an X. If these are in two separate spreadsheets, the cell references to Report 2 will look a little different than just $C$2:$C$4. They will have the tab name or workbook name as part of their addressing. For example, if Report 2 is on Sheet 2, then the addressing would look like: Sheet2!$C$2:$C$4

    Hope this helps.
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Compare two sheets to find matches?

    Put this in E2 of the Report1 sheet:

    =IF(COUNTIF('Report 2'!C:C,B2)>0,"X","")

    then copy down. It just counts the number of occurrences (of the initials on that row) in column C of the other sheet - if there is at least 1 then the cell is marked with an X.

    Another way of doing it is:

    =IF(ISNUMBER(MATCH(B2,'Report 2'!C;C,0)),"X","")

    Hope this helps.

    Pete

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Compare two sheets to find matches?

    So we are matching up Initials as that's the only thing that matches on the two sheets

    In report 1 E2
    =IF(ISNUMBER(MATCH(B2, 'Report 2'!$C$2:$C$10000,0)), "X","")
    copied down.
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    05-03-2012
    Location
    Plainsboro, NJ
    MS-Off Ver
    2010
    Posts
    20

    Re: Compare two sheets to find matches?

    This:

    =IF(ISERROR(MATCH(B2,$C$2:$C$4,0)),"","X")

    seems to be working!!

    YAYYYYY!!

    I can always depend on this forum to help with persnickity Excel problems.



    Thank you very much,
    Cindi

  6. #6
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Compare two sheets to find matches?

    Glad to help. Have a great weekend!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1