Using Macro to reconcile different sheets

  • I have 4 different excel sheets that im trying to pull data from.

    I am trying to pull these specific columns from each sheet to compare against each sheet to find any discrepancies.

    • Manager
    • Employee Name
    • User ID
    • Cost Center
    • Start Date

    What formula or code do i use to gather the different columns from each sheet together?

    Is their a code or formula i can do that once i have pulled the data columns from these different sheets that I can reconcile them and find whats missing or different.

    If im not making sense, don't hesitate to advise me.

  • Re: Using Macro to reconcile different sheets

    The best way to do it would be to have separate sheets for each of the components (i.e. a sheet for Manager, a sheet for Employee Name etc.). Then follow the below (using Manager as an example, but repeat the same process for the rest).

    1. Create a master list of all managers and list them in a column with header "ALL MANAGERS"
    2. Copy and paste all the managers from each sheet into this column, then remove duplicates (select range then ALT+A+M)
    3. Create more columns to reference the multiple sheets (i.e. "Managers Sheet 1", "Managers Sheet 2", etc. etc.)
    3. Use the MATCH function to reconcile the managers in the master list with all the sheets. MATCH will return a row number if found, and an #N/A if not

    I have attached a spreadsheet as an example.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!