Summing up data from multiple columns when columns exist

[suhassharma1010]

Hi Everyone,

I'm trying to create multiple files based on 2 input files. One file has entity ID and corresponding relevant columns for example 

entity 1	C1
entity 1	C2
entity 1	C5
entity 2	C2
entity 2	C3
entity 3	C1
entity 3	C4

The second table is

C1	C2	C3	C4	C5
1	23	4	6	34
2	21	2	23	32
3	3	7	78	87

The output will be one file for each entity containing columns relevant to them i.e. file for entity 1 will have columns C1, C2 and C5 and so on. I have been successful in doing this using a macro. The problem is that I want each file to have a column that will have the sum of the values from all other columns. Has anyone come across an issue such as this? If yes, please share your approach.

[rzdodson]

@suhassharma1010 are you needing it to have Row, Column, or Row and Column totals for the final output?

[Raj]

please share the expected output

[rzdodson]

@suhassharma1010 if I understand your requirements correctly, you can accomplish this with an iterative macro utilizing this approach (see below). The iterative macro will go through each entity, retrieve the C values for that entity, create a Total column at the entity level, then output the data associated with that entity in the same directory as where the original workflow was saved.

[CoG]

Somewhat silly, but dynamic approach. In your macro (at the end): ensure that you have a RecordID column, Transpose the Data, then Immediately Cross Tab adding in a "Total Column" under Method for aggregating values.