Column headers names to cell values

Question

Hello everybody,

I hope that this is the correct place to put my question as this is my first time posting.

I have a situation where the columns headers names represent the percentage of which the values of the columns need to be applied with.

Eventually, I want to create a new column with the sum of those columns after the data of them have been multiplied with the corresponding percentage. As the example below. The other thing is that while the data format and structure that I am receiving is consistent, is changing dynamically per week, so next week the columns headers names will change.

Any help will be much appreciated.

Thank you very much.

EXAMPLE.xlsx

Qiu · Accepted Answer

@Stelios

Dynamic as you request😁

danilang · Accepted Answer

Hi @Stelios

Here's a completely different way to accomplish the same thing

It uses the Dynamic Replace tool which has a special mode to allow it to treat the values coming into the R input as formulas and evaluate them placing the result in a field.

The bottom branch here builds up the final formula to be evaluated by first building the formula for each Percent column and then concatenating the results into a single string.

The results are the same as the excellent solution provided by @Qiu without requiring the transpose, join and summarize tools

Dan