Forum Discussion

Mike Schulman's avatar
Mike Schulman
Copper Contributor
Mar 28, 2025
Solved

Consolidate Schedules in Excel

I have multiple schedules in different worksheets that I want to consolidate into one master schedule.  I tried following the steps in the "Consolidate Data in Multiple Worksheets" help file, but not...
excel
  • Kidd_Ip's avatar
    Kidd_Ip
    Mar 28, 2025

    It might not always handle date formats or non-numeric data as expected but some alternative approaches:

     

    1. Using Power Query

    Power Query is a robust feature in Excel for combining data from multiple sheets:

    • Go to the Data tab and select Get Data > From Other Sources > Blank Query.
    • In the Power Query Editor, use the formula =Excel.CurrentWorkbook() to load all tables in your workbook.
    • Combine the tables by selecting the columns you want and clicking Close & Load to create a consolidated table.
    1. Manually Copy-Pasting with Sorting

    Since you mentioned that copy-pasting and sorting isn't too cumbersome for your dataset, you may consider:

    • Copy the data from each sheet into a master sheet.
    • Use Excel's Sort & Filter tools to organize the data by date, time, or any other column.
    1. Using VBA (Macros)

    If you're comfortable with VBA, you can write a macro to automate the consolidation process:

    • Open the Developer tab and select Visual Basic.
    • Insert a new module and paste a VBA script to loop through your sheets and copy data into a master sheet.
    1. Check Date Formatting

    If the Consolidate function is showing dates far in the future (like 2275), it might be due to inconsistent date formats across your sheets. Ensure all date columns are formatted as Date in the same format (e.g., MM/DD/YYYY).

resourcefulmynd's avatar
resourcefulmynd
Copper Contributor
Mar 28, 2025

Hi Mike Schulman, try the steps below to consolidate multiple worksheets in a workbook.

1.Convert the worksheets to a table using Ctrl + T

2. Go to Data Tab

3. Click on Get Data >> From other Sources >> Blank Query

4. In PowerQuery Editor, click on Advanced Editor

5. In the Advanced Editor in front of the "=" sign type "Excel.CurrentWorkbook()" and click okay

6. In the column titled name, select the filter and choose the files you want to combine together and then right click on the column and remove it.

7. Go to the content tab and click on the icon at the top right and uncheck the box "Use original column as prefix"

8. Go to the Home tab and at the top left of the click on "Close and load" and select table.

Regards.

Resources