Jun 08 2021 01:19 PM
I am looking for help on how to consolidate data and expenses from multiple worksheets into one worksheet. All of the worksheets have identical labels in the first row. The rows below the header row aren't consistent because each person's invoice is different and everyone doesn't work the same amount of time. I need the data (School affiliation) as well as the expenses under the various columns. I am not fluent with this type of analysis and have tried a variety of things but haven't been successful. I have some analysis and pivot tables, but not the exact one that I need. Thanks
Jun 27 2021 08:48 PM
Jun 29 2021 09:08 AM
Jun 29 2021 04:46 PM
Hi @Obie76,
Step1:
Created a folder where I can drop the csv files.
In this example the folder is called Sheets:
Then I made a copy of each trainer name sheet in your sample workbook into another workbook and saved it as a csv file with their name as the filename:
then I created a new workbook where I can consolidate all the csv file in power query, query by folder:
Select the folder and click open:
a dialog box will pop up:
Click the arrow and select combine and transform:
a dialog box will pop up, click OK:
The query editor will pop up, this is where you clean your data. What I did to your data was to filter out the null values in Inv Submit Date from this:
To this:
to this:
Then I click close and load:
and viola you got your all trainers data:
cheers