+ Reply to Thread
Results 1 to 4 of 4

Find differences between 2 excel sheets or tables

  1. #1
    Registered User
    Join Date
    02-19-2015
    Location
    Calgary
    MS-Off Ver
    2010
    Posts
    3

    Find differences between 2 excel sheets or tables

    Hi,

    I am trying to find differences between 2 super large excel sheets. I have a table in Sheet 1 and an almost identical table in Sheet 2. Sheet 2's table has cells with changed or deleted values though. I want to highlight or know about changes between Sheet 2 and 1. I found the excel VBA code below on the internet:

    Please Login or Register  to view this content.
    It almost works. The problem is that it highlights way too many cells whenever a row in Sheet 2 will have disappeared compared to Sheet 1. The code above compares the 2 sheets with respect to the content of cells relative to their position in the sheet (e.g. Cell B18 in Sheet 1 == Cell B18 in Sheet 2??). I would like it to compare the values in the 2 tables based on the ID number in the very left column of both tables. That way, if a row is deleted in the table from sheet 1 to sheet 2, I will not have all cells highlighted in Sheet 2 from that deleted row downwards.

    I attached a simplified version of my excel sheet tables. The real one has thousands of rows. test1.xlsm

    Thanks!

    FBG
    Last edited by FDibbins; 02-19-2015 at 06:33 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Find differences between 2 excel sheets or tables

    Hi, welcome to the forum

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I have added them for you - this time

    OK, having said that...

    How and where do you want the differences shown? Can you provide a sample of how you want them shown please?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-19-2015
    Location
    Calgary
    MS-Off Ver
    2010
    Posts
    3

    Re: Find differences between 2 excel sheets or tables

    Hi FDibbins,

    Thanks for the welcome! I apologize for not respecting the rules. As you realized, I am pretty new at this. Thanks for editing my post.

    Back to the issue, I like how differences are highlighted in yellow in Sheet 2 with the current VBA code above. It is pretty convenient and visually easy to spot. Unfortunately, it is impossible to highlight a row that has disappeared in Sheet 2. For deleted rows (that are in Sheet1's table, but not in Sheet 2's), I think a good way to analyze these would be to copy and paste them from Sheet 1 to Sheet 3. This way any value differences for a given ID would be highlighted in yellow in Sheet 2's table. Deleted IDs and their corresponding table values would be shown on Sheet 3. New IDs and table rows could also be highlighted in Sheet 2 (the new lines at the bottom of the table relative to Sheet 1).

    This idea is just a suggestion. It could also be equally good to have deleted rows highlighted in blue or another color on Sheet 1. The end goal really is to know what values have changed, disappeared or appeared with to a given ID between the 2 tables.

    Hope this makes sense!

    Thanks a lot!

  4. #4
    Registered User
    Join Date
    02-19-2015
    Location
    Calgary
    MS-Off Ver
    2010
    Posts
    3

    Re: Find differences between 2 excel sheets or tables

    I am thinking of doing some sort of VLOOKUP between the 2 tables on the ID column (very left) to see which ID has disappeared. I am afraid this will take too much juice out of my processor though as there are some many cells involved... Hence the VBA code above...

+ 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. [SOLVED] Need formula or Macro to help find differences between two tables of data
    By geckony in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-08-2014, 05:27 AM
  2. Find Differences between columns sheets
    By dalerdd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2013, 02:44 PM
  3. how to find differences between 2 excel sheets
    By remdog in forum Excel General
    Replies: 7
    Last Post: 09-11-2012, 07:16 AM
  4. Macro to find differences between 2 sheets based on cells matching
    By mauricio1013 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-27-2012, 04:03 PM
  5. Replies: 1
    Last Post: 07-12-2012, 05:43 AM

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