Add columns based on their Name + 1 column to the right

Question

Hi,

I have a table with multiple product IDs and date columns - I need to sum up the column "Initial" and the column to its right.

The issue is that, the column to the right of "Initial" is dynamic, it changes every month.

How can I dynamically select "Initial" + 1 column to its right, and then sum them up to get the column highlighted in yellow?

I am trying to avoid using Field Number as well as I am not sure if column positions will change.

Thank you!

sample file attached as well

test.xlsx

binu_acs · Answer

@teeefarnee If the position of the columns not changing then you can use the dynamic select tool to achive this

DynamicSelect_091023.yxmd

JamesCharnley · Answer

Hi @teeefarnee

There could be a way to do this with dynamic select but I can't think of it right now but I'll avoid it for now if we can't use Field Number. An approach like the following would work using the field info tool to identify the column to the right of initial, leveraging pivots to be able to aggregate with a field name of 'Value' and joining everything back on. If you wanted the rest of the columns still you can join them back on using the record ID

Initial and First Period.yxzp