Summing up data from multiple columns when columns exist

Question

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 1C1entity 1C2entity 1C5entity 2

C2entity 2C3entity 3C1entity 3C4

The second table is

C1C2C3C4C51234634221223323377887

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.

CoG · Answer

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.

rzdodson · Answer

@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.

Solution.yxzp

Raj · Answer

please share the expected output