Creating new columns based on multiple columns

CFuser87

Currently, I have various costs listed in excel by column.

Goods	Selling Type Cost 2024	Selling Type Cost 2025	Selling Type Cost 2026	Selling Type Cost 2027	Selling Type Cost 2028	Selling Type Cost Total	Admin Type Cost 2024	Admin Type Cost 2025	Admin Type Cost 2026	Admin Type Cost 2027	Admin Type Cost 2028	Admin Type Cost Total
Alpha	200	100	0	100	200	400	100	50	0	50	100	200
Beta	400	200	0	200	400	800	200	100	0	100	200	400

Ideally, I can transform this information into the following.

Goods	Total Cost 2024	Total Cost 2025	Total Cost 2026	Total Cost 2027	Total Cost 2028	Total Cost
Alpha	300	150	0	150	300	600
Beta	600	300	0	300	600	1200

I've tried using multi row formula, but couldn't figure it out. Is there an easy way to do this without manually adding each year cost together using the formula tool.

Adding columns.xlsx

DataNath

Hey @CFuser87, how does something like this look?

To outline the steps we:

1) Use a Dynamic Select to remove the 'Total' fields you have already

2) Transpose the data to get the field names into a single column ready for parsing

3) Parse the year from the field names

4) Cross-Tab, grouping by the year and using Sum as the aggregation, also ticking to add a Total Column

5) Dynamic Rename and Sort just to tidy things up into the desired format

The only thing is the totals are different to what you outlined in your expected outcome - in the example above your years add up to more than the 'Total Cost' so not sure if this was an error or there's extra logic to add here.

Creating new columns based on multiple columns.yxmd