Transposing data

[Chelseaa]

Hi all, need some help with transposing again and some formula

I have my input data here:

Region	Month	Product Cost	Transport	Savings	Mktg cost	Adjustments
AMER	Jan	3	4	-1	2	3
AMER	Feb	2	3	-2	2	2
AMER	Mar	2	2	0	5	1
APAC	Jan	6	5	-1	2	2
APAC	Feb	7	2	-0.5	3	4
APAC	Mar	5	3	0	6	2

and the output i would like is this (column 2 is formula based and i put it in algebra form):

Region	Month	Types of costs	Column 1	Column 2
AMER	Jan	Product Cost	a
AMER	Jan	Transport	b	a-b
AMER	Jan	Savings	c	b-c
AMER	Jan	Mktg cost	d	c-d
AMER	Jan	Adjustments	e
AMER	Feb	Product Cost	f
AMER	Feb	Transport	g	f-g
AMER	Feb	Savings	h	g-h
AMER	Feb	Mktg cost	i	h-i
AMER	Feb	Adjustments	j
APAC	Jan	Product Cost
APAC	Jan	Transport
APAC	Jan	Savings
APAC	Jan	Mktg cost
APAC	Jan	Adjustments
APAC	Feb	Product Cost
APAC	Feb	Transport
APAC	Feb	Savings
APAC	Feb	Mktg cost
APAC	Feb	Adjustments

Thank you!

[AkimasaKajitani · Accepted solution]

Hi @Chelseaa 

I made 2 pattern solution.

Pattern 1 is simple solution. But if the column sort order is changed, it will be not work.

Pattern 2 is dynamic solution. If column order is changed, it will work well.

[atcodedog05]

Hi @Chelseaa 

Here is my take on it.

Multi-row formula

IF [Name] IN ("Transport","Savings","Mktg cost") 
THEN [Row-1:Value]-[Value]
ELSE Null()
ENDIF

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Chelseaa]

@AkimasaKajitani @atcodedog05 i prefer the more dynamic solution, thank you both for your help!