The trouble is, working with Data Consolidation can be tricky! Data Consolidation is an incredibly useful tool that help you understand and present your data quickly and easily.
The screen may look daunting but like all great things, good planning is the key.
The data is broken down into quarters and stored in one year per worksheet in one workbook.
The only change you’ll make is with regard to the workbook and sheet names, so for this article we’ll assume you’re combining data from within the same workbook.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.If you are using an older version (Excel 2003 or older), this tip may not work for you.In our example, we have data for 3 years expenditure on tea, coffee and milk.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.
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.
Each worksheet may not have the same survey questions. I'm assuming it is because each worksheet has a name and are not sequential (although chronological).
Click in the Reference area and select the first data range to consolidate – to do this you will need to click the Sheet tab i.e.
“Year 1” and then drag over the data (including row and column headings) and then click the Add button to add this first set of data to the consolidation dialog.
Data Consolidation allows you to gather together your data from separate worksheets into a master worksheet.