+ Reply to Thread
Results 1 to 4 of 4

Find like numbers over two spreadsheets and highlight

  1. #1
    Registered User
    Join Date
    03-16-2014
    Location
    OH
    MS-Off Ver
    Excel 2007
    Posts
    2

    Find like numbers over two spreadsheets and highlight

    I have two spreadsheets. Spreadsheet 1 has 6 columns and column E has a list of numbers. It runs from E2:E739. Spreadsheet 2 has 9 columns and column A has the numbers I need matched against. Column A runs from A9:A5027. How can I find the numbers from Spreadsheet 1, Column E that are also in Spreadsheet 2, Column A. Once matched I would like to highlight the ROW on spreadsheet 1 that has a match.

    Your help is appreciated.

    thanks
    JM

  2. #2
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,017

    Re: Find like numbers over two spreadsheets and highlight

    You can use the MATCH() function to attempt an exact match of any values on sheet1 that exist on sheet 2, the result would be the row number where that match can be found:

    Sheet1 cell G2: =MATCH($E2, Sheet2!E:E, 0)

    Copy that down. If a number appears, there is a match on the row number given. If an error returns, no match.

    I think that's clear enough, numbers and errors highlight the matches clear enough. If you still want colors, you can use Conditional Formatting to highlight any rows that have a number in the new column G.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    03-16-2014
    Location
    OH
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Find like numbers over two spreadsheets and highlight

    I did the above substituting sheet 2 with the actual sheet 2 name and all I get is n/a. I manually looked up a couple of numbers and there are quite a few that would match.

  4. #4
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,017

    Re: Find like numbers over two spreadsheets and highlight

    Then the numbers on each sheet are not exact matches.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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