+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Compare two sheets of alpha numerical data and produce a report with the differences

  1. #1
    Registered User
    Join Date
    07-15-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    1

    Compare two sheets of alpha numerical data and produce a report with the differences

    Hi all

    I've been landed with a huge task for a relative Excel newbie, which is trying to speed up the auditing of two systems i use at work (by speed up, i mean stop doing a manual audit!).

    For some background, I have a new online system and a separate established spreadsheet, both contain the same personal data and need to match. Either can be updated independently of the other and so need to be audited each week to ensure consistency. Keep in mind, i won't know WHICH set of data is correct, I just need to be able to tell people which of their staff has inconsistent data held about them so they can sort it out.

    The focus is very much on getting the new online system to match the current spreadsheet - the spreadsheets have been in use so long that everyone updates them by habit - so it is likely the online system data which will be wrong or incomplete.

    The online system can export raw CSV data which i then import into Excel, and after a bit of column re-naming and formatting, i have two sheets of data which are directly comparable.

    The columns i have are are text apart from Staff number:

    A to G: Name, Staff number, job title, location, supplier, team manager, ops manager.

    What i want to do:

    1. Check the data for each cell in MasterList matches that in the ExportList
    2. Copy any rows which contain cells that don't match exactly from the ExportList into a 3rd spreadsheet AuditReport
    3. Highlight the cell with the inconsistent data on AuditReport in a colour so i know what needs to be investigated.

    Even just comparing the data on both sheets and highlighting the cells which are inconsistent on the ExportList would be a godsend to me - i could then work with this to get the highlighted rows only and build a report from there.


    I literally have no idea where to begin - i'd thought about a hlookup but i believe the data has to be numeric? Or use if then else code? (again - does this work with text?).

    I don't expect someone on here to completely do my job for me... Any help would be greatly appreciated to get me started - even if its just pointing me in the direction of the Excel function i should be using and then looking at any code i come up with would be fantastic - there aren't any excel experts i can go to for help other than on somewhere like this forum.

    Thanks

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,209

    Re: Compare two sheets of alpha numerical data and produce a report with the differences

    Hi chrisola and welcome to the forum,

    Here is a sheet I did a few days ago for another poster. It uses conditional formatting to show differences between two sheets. This should be a start for you.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ 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