+ 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
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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)

Similar Threads

  1. Find and highlight two opposite numbers by multiple criteria(A+B=0)
    By bumbar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-26-2017, 02:18 AM
  2. [SOLVED] find numbers from one sheet on new sheet and highlight
    By vitt4300 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-29-2014, 10:30 AM
  3. Automatically find and highlight common numbers in multiple coulmns.
    By cyber_9 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 06-22-2012, 10:34 AM
  4. Find and Highlight specific numbers
    By RWheldon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2009, 07:34 AM
  5. Find and Highlight a group of numbers
    By Krazy Kasper in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-28-2008, 04:17 PM

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