I would like to take the before spreadsheet and turn it into the after. I am guessing Transform and Cross Tab will be used but i have been unsuccessful. I need to pivot off all the fields in rows 1, 2 and 3 except field XYZ_00. Before: PQ_01 RS_07 XYZ_00 Budget Budget Budget Budget Mar Jun Sep Dec ABCD1234 GH123 149,745.75 0 167,132.70 0 After XYZ_00 Mar Budget RS_07 PQ_01 ABCD1234 GH123 149745.8 Jun Budget RS_07 PQ_01 ABCD1234 GH123 0 Sep Budget RS_07 PQ_01 ABCD1234 GH123 167132.7 Dec Budget RS_07 PQ_01 ABCD1234 GH123 0 Thanks for any help that can be provided.

Transform and pivot

I would like to take the before spreadsheet and turn it into the after. I am guessing Transform and Cross Tab will be used but i have been unsuccessful.

I need to pivot off all the fields in rows 1, 2 and 3 except field XYZ_00.

Before:

		PQ_01	RS_07	XYZ_00
		Budget	Budget	Budget	Budget
		Mar	Jun	Sep	Dec
ABCD1234	GH123	149,745.75	0	167,132.70	0

After

						XYZ_00
Mar	Budget	RS_07	PQ_01	ABCD1234	GH123	149745.8
Jun	Budget	RS_07	PQ_01	ABCD1234	GH123	0
Sep	Budget	RS_07	PQ_01	ABCD1234	GH123	167132.7
Dec	Budget	RS_07	PQ_01	ABCD1234	GH123	0

Thanks for any help that can be provided.

Transformation

Accepted answers

patrick_digan

@mbeck I went back to the drawing board, and I'm much happier with the workflow now. It will allow you to have as many rows as you want. It's also much cleaner.

Let me know if you have any questions!

Transform and Pivot.yxmd

All comments

patrick_digan

@mbeck You were right on track that you needed a transpose and then a crosstab. If you add a recordid, you could do a transpose and then a crosstab and get most of the way there. To get the rest of the way, I've done a lot of monkeying around before the crosstab. I've made a lot of assumptions that the size of your data won't change.

Hope that helps!

Transform and Pivot.yxmd

mbeck

Thank you very much for putting in the time to solve this. I will review and let you know if this works for all the scenerios i have. unfortunately the number of rows can change but hopefully I can modify what you have done to make it work.

Thanks again!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703