Forum Discussion
Solved
Merge Excel workbooks and create a summary
HI All,
Looking for merging the excel files for different vendors and create one summary file.
I have 30 different excel files with the same format and want to merge a small portion (Highlighted in Red) and create a summary. Kindly support
Expected Output
Thank you in advance
kheldar Well maybe here is another function for you to learn 🙂 FILTER(). It is new with the introduction of dynamic arrays. There is also some other great new functions including UNIQUE, SEQUENCE, and LET to just name a couple.
=FILTER(Regulated[Name],Regulated[Shift]=TEXT($M$2,"hh:mm"),"none")note I had to add the TEXT() to your M2 because the shift time is converted to text. I would recommend getting rid of that and just format that column to display in that format instead, but that is just me.
9 Replies
Hi Chris525
For this kind of things it's better to share a sample file as it's not clear if the info. you want to extract is in the first 7 rows. Assuming it's in the first 7 rows of each 'Sheet1':
- Store your 30 files in a folder
- Download & open the attached file
- In sheet PARAM replace 'C:\Lz\Downloads...' with the path to the folder containing your 30 files
- Switch to sheet Summary
- Right-click in the blue table > Refresh
Hi Chris525
I cannot determine the source of the error with a screenshot only. What I shared is done with Power Query (aka Get & Transform). I need to know on which query step you get the error:
- Go to Excel Data tab > Queries & Connections
- Double-click on 'SuppliersSummary' in the Queries & Connections pane (Power Query Editor will open)
- In APPLIED STEPS (on the right) click each step, one by one, until you get the error and give me the step name on which you get the error
(I might ask you to share your 30 files as the source of the error is due to at least one of them)
