How to Transpose multiple headers to rows

[vishwanathans]

Hello,

I am trying to figure out a way to transpose multiple headers to a row field.

Here is my data:

		MTD	MTD	MTD	MTD	MTD	MTD
		Actual	Actual	Actual	Actual	Actual	Actual
		Group A	Group A	Group A	Group A	Group A	Group A
		Jan	Feb	Mar	Apr	May	Jun
		FY19	FY19	FY20	FY20	FY21	FY21
Total CostType	Revenue	100000	100000	100000	100000	100000	100000
DirectCost	Salary	50000	50000	50000	50000	50000	50000
DirectCost	Bonus	2000	2000	2000	2000	2000	2000
DirectCost	Healthcare Cost	1500	1500	1500	1500	1500	1500

I want the result to be :

Total CostType	Revenue	MTD	Actual	Group A	Jan	FY19	100000
Total CostType	Revenue	MTD	Actual	Group A	Feb	FY19	100000
Total CostType	Revenue	MTD	Actual	Group A	Mar	FY20	100000
Total CostType	Revenue	MTD	Actual	Group A	Apr	FY20	100000
Total CostType	Revenue	MTD	Actual	Group A	May	FY21	100000
Total CostType	Revenue	MTD	Actual	Group A	Jun	FY21	100000
DirectCost	Salary	MTD	Actual	Group A	Jan	FY19	50000
DirectCost	Salary	MTD	Actual	Group A	Feb	FY19	50000
DirectCost	Salary	MTD	Actual	Group A	Mar	FY20	50000
DirectCost	Salary	MTD	Actual	Group A	Apr	FY20	50000
DirectCost	Salary	MTD	Actual	Group A	May	FY21	50000
DirectCost	Salary	MTD	Actual	Group A	Jun	FY21	50000
DirectCost	Bonus	MTD	Actual	Group A	Jan	FY19	2000
DirectCost	Bonus	MTD	Actual	Group A	Feb	FY19	2000
DirectCost	Bonus	MTD	Actual	Group A	Mar	FY20	2000
DirectCost	Bonus	MTD	Actual	Group A	Apr	FY20	2000
DirectCost	Bonus	MTD	Actual	Group A	May	FY21	2000
DirectCost	Bonus	MTD	Actual	Group A	Jun	FY21	2000
DirectCost	Healthcare Cost	MTD	Actual	Group A	Jan	FY19	1500
DirectCost	Healthcare Cost	MTD	Actual	Group A	Feb	FY19	1500
DirectCost	Healthcare Cost	MTD	Actual	Group A	Mar	FY20	1500
DirectCost	Healthcare Cost	MTD	Actual	Group A	Apr	FY20	1500
DirectCost	Healthcare Cost	MTD	Actual	Group A	May	FY21	1500
DirectCost	Healthcare Cost	MTD	Actual	Group A	Jun	FY21	1500

Thank you so much for your help.

Srini

[jamielaird · Accepted solution]

Hey @vishwanathans ,

That was a bit of a mind-bender..!

Here's one approach that works:

[mceleavey]

Hi @vishwanathans ,

I agree with @jamielaird , I couldn't get my head around this to begin with, but I've built a bit of transpose magic to do the job:

I've also attached a macro I built to do the grouped record ID.

Hope this helps,

M.

[vishwanathans]

Thanks Jamie, Thats a perfect fix.

You rock 🙂

[vishwanathans]

Hello @mceleavey,

Thank you so much for your help. Macro was really helpful.

Thanks,

Srini

[mceleavey]