Multiple Excel files, many sheets and identifier on first sheet

Jonne35

New Member
Joined
Sep 29, 2017
Messages
1
Hello,

I have been reading this forum for years and have found amazing amount of valuable information presented in a clear way, many thanks for every contributtor for that. Now I have started to study Power BI and I have already faced some difficulties while trying to build my data model.


The problem I'm trying to solve is the following:


I have multiple Excel files (20ish) and each of them contains 3 to 5 worksheets.

In each of them the sheets are named using same coding (S01-S05).

S01 is always identical to the S01 in other workbooks and so on but worksheet S01 to 05 are all different, i.e. they are indentically structured files with difference between worksheets.

S01 always contains the identifier data which is the name of the reporter and other codes on other rows. Other sheets besides S01 contain no reporter identifier.


What I'm trying to achieve is a model that would allow me to compare reporting of different users. E.g. I would like to see a list of data reported in worksheet S03 row 20 column 40 for all the 20 different reporters.

Do you think this kind of model should be built using Power BI or Access if I have to occasionally update a new Excel file from one of the 20 users to replace the former?


Hopefully I managed to present this problem in a way that can be understood by some of you at least.


Thank you in advance if someone is able to provide some help with this issue!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sorry to only reply that I have a similar issue, and wanted to check if you've come up with a solution. I believe Power Query will bring this data together as you require, but I'm just trying to come up to speed myself.
 
Upvote 0
This is perfect for Power BI and you don't need Access. There are some benefits of loading to Access first, it it really doesn't matter unless the refresh time becomes an issue.

Ready article here explaining the manual process of combining files. I would write 1 function for each sheet, then combine each set of sheets with a second set of queries.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top