How to total a column in another file based on the data from a file?

Question

Hello Friends

I have 2 Excel Workbooks.

Excel One. Has Cost Centre & Revenue Cost Centre

Cost CenterRevenue CCA220418ABC23C220603A220418ABCDE23C220603A220418ABCDEFG23C220603A220418000023C220603A2204180000123C220603A2204180001023C220603A2204180010023C220603A22041800100023C220603A220418010000023C220603A22041850023C220603A2204185670023C220603A2204145666623C220603A220415000023C220603A220416000023C220603

Excel Two - Has Cost Centre & Value

Cost CenterValueA220418ABC300A220418ABCDE455A220418ABCDEFG57244A2204180000114033A22041800001170822A22041800010227611A22041800100284400A220418001000341189A2204180100000397978A220418500454767A22041856700511556A22041456666568345A2204150000625134A2204160000681923

The value for each cost centre is in Excel Two.

There are multiple Revenue & Cost centres in both the Excels.

THE COST CENTRE IS UNIQUE

The requirement.

For every Revenue Cost Centre, I must get the corresponding Cost Centre in Excel One, read all the Cost Centres in Excel Two, get the corresponding Value and total them.

Expected Output

23C220603 IS THE REVENUE CC in Excel One

4435757 IS THE TOTAL VALUE OF THE REVENUE CC in Excel Two.

23C2206034435757

davidskaife · Accepted Answer

Hi @Kallis

Thanks for updating the data, it makes more sense now. Please see the attached as one way of doing this:

Join Excel One to Excel Two using Cost Centre as the join condition, then using the Summerize tool group on Revenue CC and Sum the Values.

Workflow attached

Kallis · Accepted Answer

Thanks @davidskaife

It worked like a charm