Combine sheets from different workbooks Horizontally

Hello Everyone,

I am working on consolidating the financials of different units of a Company where the requirement is to stack the financials of all the units Horizontally and then make a total of it. The financials of each unit are in separate workbooks. The data of each unit's financials is in 3 columns, the first column contains the name of the "head of account". The Second column is the "amount" column and the third one is the percentage of the figures of column 2 calculated as a percentage of the total income.

The head of accounts must follow a specific sequence and sub-totalling. Some units may not have certain heads of accounts in the report. The exhaustive consolidated sequence of the heads of accounts is maintained in a template against which the figures and percentages of each of the units are to be mapped for the reporting. A total of the financials is to be made at the end.

I tried to approach this through 2 methods, one is by importing each workbook individually using a separate input tool and then using a "Multi join tool which was not very effective since it would mean sanitizing the data from each input tool due to blank rows. Also, the multi join tool changed the entire sequence of the head of accounts which cannot happen.

The other approach was importing all the data through a single input tool using wildcard and then using the cross tab tool 2 times to get the data of all units into separate columns, one cross tab for amounts and one for percentage. Again the issue here is that the cross tab tool completely changed the sequence of the first column. Even if I apply a join tool to one of the cross tab tools with my masster template, the join tool also changes the sequence.

While there are 11 units, the data of which needs to be combined, I have attached 3 sample workbooks containing sanitized financials of units individually and the desired output.

Would really appreciate help with this.

Consolidated file template.xlsx

Workflow

Transformation

Join

Accepted answers

aatalai

@TapasTR start by putting all the inputs in one folder, use the directory tool and then the dynamic input tool. Then there is some transposing and crostabbing. I've uploaded a workflow that should help, you might need to perform some formatting and cleansing and reordering, but this should provide you a good starting point

Combine sheets from different workbooks Horizontally.yxzp

All comments

aatalai

Combine sheets from different workbooks Horizontally.yxzp

TapasTR

Thank you very much @aatalai - The main problem that I highlighted still persists. The order of the rows have to be in a specific order that is contained in my template. The cross tab tool changes the entire order of the data. I will try adding row ID to the template and then apply a join to see if this works and come back.

One more ask, if possible. Would it possible to add a step that can give me the data in percentage columns, in percentages like instead of 0.389871 it should show 38.99%?

jsamstad

I would use a find and replace tool to join the template column with the business units. You first have to get rid of the blanks and duplicate values to get the joins to work. You'd have to do some manipulation with the value columns if blank, but it's minor. After joining all units, you could hook that up with a text input that has all blanks to get the preferred spacing between rows.

Screenshot 2024-03-26 114835.png

Consolidated file template.yxzp

aatalai

@jsamstad inregards to the % use multifield tool set it to string

Tostring([currentfield]*100)+"%"

TapasTR

@aatalai - Many thanks

TapasTR

@jsamstad - Many thanks for all the efforts. This brought new perspective to the ways I had applied. Only problem, is that this workflow merges only gets one of the units to the desired format. The main part of the query was to bring the 2 columns from all workbooks into this sheet horizontally.

jsamstad

Yes you can handle that different by duplicating the flow for the one unit and then updating the input + adding another find & replace tool. There is some leg work that has to be done on your part to tailor the solution to your situation, but the outline should provide a guide how to do it.

Once you can figure out how to do it with static input, try to implement a more dynamic solution. Cheers!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317