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

[Kallis]

Hello Friends

I have 2 Excel Workbooks. 

Excel One. Has Cost Centre & Revenue Cost Centre

Cost Center	Revenue CC
A220418ABC	23C220603
A220418ABCDE	23C220603
A220418ABCDEFG	23C220603
A2204180000	23C220603
A22041800001	23C220603
A22041800010	23C220603
A22041800100	23C220603
A220418001000	23C220603
A2204180100000	23C220603
A220418500	23C220603
A22041856700	23C220603
A22041456666	23C220603
A2204150000	23C220603
A2204160000	23C220603

Excel Two - Has Cost Centre & Value

Cost Center	Value
A220418ABC	300
A220418ABCDE	455
A220418ABCDEFG	57244
A2204180000	114033
A22041800001	170822
A22041800010	227611
A22041800100	284400
A220418001000	341189
A2204180100000	397978
A220418500	454767
A22041856700	511556
A22041456666	568345
A2204150000	625134
A2204160000	681923

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.

23C220603

4435757

[davidskaife · Accepted solution]

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 solution]

Thanks @davidskaife 

It worked like a charm

[davidskaife]

Hi @Kallis 

Happy to help, would you mind marking my post as the accepted solution as well?

[Kallis]

Yes @davidskaife 

I have done that already