+ Reply to Thread
Results 1 to 2 of 2

Comparing two worksheets and highlighting/tracking discrepancies?

  1. #1
    Registered User
    Join Date
    08-10-2011
    Location
    Cali
    MS-Off Ver
    Excel 365
    Posts
    62

    Comparing two worksheets and highlighting/tracking discrepancies?

    So big picture, we are moving quite a bit of data from one tracking system to another (budget data). Both systems allow exporting reports to .xlsx files, so I have cooked up a macro (most extensive use of VBA thus far) to "reformat" the report from the old system and the new system so that they are effectively the same formatting for comparison sake (all columns match up, font, number formatting, etc, etc).

    What I would like to do now is fix up a way to compare a report (reformatted via macro) from the old system (which we know is correct) to the a report from the new system (which we are validating) and see what is right and what is screwed up.

    Below is an example of the data from the old system, which is our fixed reference (we know this data is good) followed by an example snipped from our new system (which may or may not have come over correctly:

    Old System DataVista Example.JPG

    New System DataCMiC Example.JPG

    As you can see, without diving into the actual data points, there is a heading in the new system data that does not exist in the old system data (1300.07 - Rent Yard Space). There also may be specific #'s in the data that do not match when correctly comparing apples to apples lines.

    So what I am looking to do is as follows:

    Global analysis of both sheets - are all the data points (not in detail) the same (IE is there a 1300.07 in the old data and the new data, in this case no there is not a 1300.07 in the old data so this is a mistake in the new data) in both reports. Basically if I were to compare cell to cell both of these reports, after a few rows they would no longer be apples to apples because in the old data, row 66 starts with 1300.08 but in the new data, row 66 starts with 1300.07 and some there is no point in comparing the following cost and budget data by direct cell reference. I do however need to highlight these high level errors ie 1300.07 exists in the new report but not the old one (and vice versa, if a line existed in the old report but not the new).

    Detailed analysis - Regardless of absolute cell reference, is the specific data in an apples to apples comparison correct? IE 1300.04 - Buy Office supplies has a quantity of 23 in column C and a unit of MMO in column D, 1300.05 has a quantity of 4 in column C and unit of EA in column D and so on and so on. Provided the data point (as reference by the code + description) exists in both reports, does the underlying data of each report match? Assuming this is going to be based on a keyword search/lookup + offset type deal.

    The ultimate project here is to run the reformatting macro on copies of probably 30 reports from each system so they line up, and then run this "comparison" macro on each pair of reports to validate what has be converted to the new system successfully and what the errors are so that we can do another conversion pass and check again. These reports will be columns A through U, and somewhere between 500 and 20,000 lines long depending on the size of the project the report came from, so upwards of 500,000 individual points to compare for errors per report pair (which is not realistic to do by hand). I would like to high cells green in each report where there is a match and red where the specific data value does not match. The missing or extra data points are proving more of a challenge for me to approach.

    Anyways, any idea where to start on this would be much appreciated. I'm poking around the web a lot to try and find a starting point but the cataloging of all mis-matched, extra, or missing data points is definitely the biggest VBA project i've taken on (could generously say I've been self teaching on/off for 3 weeks).

  2. #2
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

    Re: Comparing two worksheets and highlighting/tracking discrepancies?

    Hi

    Your data is too small to read. Do you have an excel version with the VBA you have developed so myself and others can better understand the problem

    Cheers

+ 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. highlighting changes by comparing two workbook each worksheets
    By paradise2sr in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-11-2016, 02:20 AM
  2. Find discrepancies between 2 worksheets
    By amc8468 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2014, 04:47 AM
  3. Comparing 2 Excel Columns and displaying discrepancies
    By Aralei in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2014, 09:21 PM
  4. Tracking and highlighting 'like' data in different spreadsheets
    By sallyz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-26-2013, 12:43 AM
  5. Replies: 1
    Last Post: 07-28-2012, 01:03 PM
  6. Comparing worksheets in Excel and highlighting changes
    By Michael Ubezzi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-30-2006, 08:20 PM
  7. [SOLVED] Find Discrepancies between Worksheets
    By Erin in forum Excel General
    Replies: 3
    Last Post: 11-10-2005, 11:50 AM

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