Consolidating worksheets in excel
You’ll also replace January$ and February$ with your corresponding worksheet names.
The word “Union” tells Microsoft Query that we wish to stack the data from the January worksheet on top of the data from the February worksheet.
As you will see from the drop-down, there are 11 functions to choose from.
For our data we want to add up the values so we’ll set the Function to Sum.
Patient Name, City, Date of Service, Provider, etc are all in the same cell.5. When it's all done I will only have "555555" in the cell.7. I would like to have all data in one Worksheet and in one table.9. The downfall with this is I could not auto fill when I went to reference the worksheet.
To do so: SELECT * FROM “E:\Combine Worksheets.xlsx”.”January$” Union SELECT * FROM “E:\Combine Worksheets.xlsx”.”February$” In this context “Select *” informs Microsoft Query that we wish to pull all of the data from the given worksheet, based on the contiguous block of cells that starts in cell A1.
Data Consolidation allows you to gather together your data from separate worksheets into a master worksheet.