+ Reply to Thread
Results 1 to 5 of 5

comparing sheets - different cell refs

  1. #1
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    comparing sheets - different cell refs

    Is there a way of comparing 2 sheets with similar data but where the data is in a different order.
    What the problem is I have two worksheets.
    The first sheet (see attachment sheet1) is how the data downloads off our main system. The second sheet (see attachment sheet2) is a form that is made for manual entry and so has some rows that are headings and also some products are sparated off because of the product type.
    This means that even though the data in the 2 columns in each sheet should be the same they will appear in different cell references.
    what I need the program to do is 1) check column B in sheet 1 to column B in sheet 2 and highlight any value in sheet 1 that doesn't appear in sheet 2.
    2) check column B in sheet 2 to column B in sheet 1 and highlight any value in sheet 2 that doesn't appear in sheet 1.
    3) A bit more more complicated. check the value in column B in sheet 1 to the value in column B in sheet 2 then lookup the value in column A of both to make sure they match and highlight the cell in column A in either sheet if they don't.
    There are diffences in the example.

    Is any of this possible as all the programs I have come accross so far only compare exact cell reference matches?
    Attached Files Attached Files
    Last edited by WasWodge; 11-20-2010 at 03:05 PM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: comparing sheets - different cell refs

    Try this..

    Please Login or Register  to view this content.
    Open up the VBA editor by hitting ALT F11

    Insert a new module by hitting Insert - Module

    Paste the macro into the empty sheet

    Hit ALT F11 to get back to the worksheet.

    Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.
    Martin

  3. #3
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: comparing sheets - different cell refs

    Thanks Martin, it is working perfectly. Can you just explain what the "N" stands for in the references before I mark it as solved.

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: comparing sheets - different cell refs

    N is simply a counter in the loop which is used to track which row is being looked at.

    The ....

    Please Login or Register  to view this content.
    .... construct is a device to work out the last populated row in a given column (in this case column 1 (A) ). I prefer to use this way of working out the last row as it allows for gaps.

  5. #5
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: comparing sheets - different cell refs

    Thanks Martin for the code and explanation. Will take it in to work Monday and adapt it on the full sheet at work (Had to take out a few columns at the start of sheet 1 for security) but that looks straightforward so all solved. Thanks again.

+ 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