Adding values in a table/matrix

[GB501]

Good evening

How would I add values of 4 tables who share the same first column holding 8 identical descriptions/cells, while the columns have the first, descriptive row and numerical values below? Some column headers are shared, others are unique.

The tables have the following layout

         E    F    G    H   I    J

A      0    2      3    1    5   1

B      0    0      7    2    3   2

C      2    3      4    6    0   0

D      0    3     4     9    1   0

         E    X     G   H   Y    I    J    W

A      4    2      3    1    5   1    3     2

B      1    0      4    2    3   2    0     3

C      0    3      4    6    0   0    1    4

D      7    3     4     9    1   0    2    1

The desired result would be:

        E    F    G    H   I    J     X     Y   W

A      4    2    6     2   6    4     2     5    2

B      1    0    11

C      0   etc.

D      7

Is it possible to summarize all cell values of all 4 tables, positions AE, AF, AG.... BE... CH...DW within respective tables?

The actual Excel file containing 4 sheets of data is in attachment.

Thank you in advance.

[JosephSerpis]

Hi @GB501 I mocked up a workflow let me know what you think?

[GB501]

I get the following messages:

Dynamic Input (2) The file "_externals\1\Table1.xlsx|||Sheet2" has a different number of fields than the 1st file in the set and will be skipped

Dynamic Input (2) The file "_externals\1\Table1.xlsx|||Sheet3" has a different number of fields than the 1st file in the set and will be skipped

And I am not sure that the numerical values belonging to skills present in all sheets have been added together, or have they?

[JosephSerpis · Accepted solution]

Hi @GB501 I mocked up another workflow and not used the dynamic input tool. Let me know what you think?

[GB501]

It seems to be working properly now, but there are two Sheet 2 specified, so it it ends up adding Sheet values twice, while skipping Sheet 3.

For Example - the Recruiting Skills ends up with value 266 (2*133, Sheet 2) for HR Specialist, while it should be 135 (0 + 133 + 2)

When I switch the second Sheet 2 to Sheet 3, I get the following notification after Running: 

Input Data (9) 0 records were read from "C:\Users\gardijan boris\Downloads\Tables_27022020 (2)\_externals\1\Table1.xlsx" (`Sheet3$`)

Can it be easily fixed? 

And modified to include 10 sheets?

[JosephSerpis · Accepted solution]

Hi @GB501 amended the workflow you will need to add an Input for each file/sheet you want to add to the table. Also currently in your example file sheet 3 has no data so hence why you are seeing 0 records.

[GB501]

Apologies, a mistake on my part (looked at a wrong source table)

The previous workflow operates flawlessly, even with an expanded data set.

Thank you very much, a life saver.