Forum Discussion
Help with consolidating data and expenses from multiple worksheets
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
3 Replies
- Thank you so very much. Any chance you can explain to me how you achieved this? I am trying to learn more about reporting and do a lot of searching on YouTube, but it doesn't always explain in a way that truly applies to what I need 100% of the way. Again, I really appreciate your assistance!
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
