Help with consolidating data and expenses from multiple worksheets

New Contributor

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

Hi @Obie76,

 

Attached is your ALLTrainerData file.

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:

Yea_So_0-1625009586280.png

 

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:

Yea_So_1-1625009695267.png

 

then I created a new workbook where I can consolidate all the csv file in power query, query by folder:

Yea_So_2-1625009785082.pngYea_So_3-1625009841927.png

Select the folder and click open:

Yea_So_4-1625009913571.png

a dialog box will pop up:

Yea_So_5-1625009955165.png

Click the arrow and select combine and transform:

Yea_So_6-1625010016350.png

a dialog box will pop up, click OK:

Yea_So_7-1625010070837.png

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:

Yea_So_8-1625010197710.png

To this:

Yea_So_9-1625010242541.png

to this:

Yea_So_10-1625010274204.png

Then I click close and load:

Yea_So_11-1625010321588.png

and viola you got your all trainers data:

Yea_So_12-1625010347425.png

 

cheers