Forum Discussion
Consolidate Schedules in Excel
It might not always handle date formats or non-numeric data as expected but some alternative approaches:
- 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.
- 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.
- 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.
- 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).
It might not always handle date formats or non-numeric data as expected but some alternative approaches:
- 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.
- 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.
- 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.
- 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).
Thanks, the Power Query option worked great. I had to figure out how to exclude some tables and columns, but that was fairly easy. So was the date and time issue. I just had to reapply my formats to the query table. What I can't figure out is how to keep the data current when one of the source tables gets updated. I tried Data > Queries & Connections > refresh All, but that didn't seem to work. Is there a way to trigger a refresh, or better yet, a way to make it dynamic so it refreshes automatically?
Thanks, the Power Query option worked great. I had to figure out how to exclude some tables and columns, but that part was fairly easy. What I can't seem to find is a way to refresh the data when one of the source tables gets updated. I tried Data > Queries & Connections > Refresh All but that didn't seem to work. Is there a way to refresh the query, or even better, to make it dynamic?
