Transform and pivot

[mbeck]

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.

[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!

[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!

[mbeck]

sorry but i have tried to modify the logic without any luck.   I appologize as i should have given more data.   below is a more realistic sample with more data. 

Before

		PQ_01	RS_07	XYZ_00
		Budget	Budget	Budget	Budget
		Mar	Jun	Sep	Dec
ABCD1234	GH123	149746	0	167133	0
CDEEF5678	GH123	0	68382	102762	22274
ABCD1234	IH456	18900	0	0	0
CDEEF5678	IH456	10575	5175	4725	0

After

						XYZ_00
Mar	Budget	RS_07	PQ_01	ABCD1234	GH123	149746
Mar	Budget	RS_07	PQ_01	CDEEF5678	GH123	0
Mar	Budget	RS_07	PQ_01	ABCD1234	IH456	18900
Mar	Budget	RS_07	PQ_01	CDEEF5678	IH456	10575
Jun	Budget	RS_07	PQ_01	ABCD1234	GH123	0
Jun	Budget	RS_07	PQ_01	CDEEF5678	GH123	68382
Jun	Budget	RS_07	PQ_01	ABCD1234	IH456	0
Jun	Budget	RS_07	PQ_01	CDEEF5678	IH456	5175
Sep	Budget	RS_07	PQ_01	ABCD1234	GH123	167133
Sep	Budget	RS_07	PQ_01	CDEEF5678	GH123	102762
Sep	Budget	RS_07	PQ_01	ABCD1234	IH456	0
Sep	Budget	RS_07	PQ_01	CDEEF5678	IH456	4725
Dec	Budget	RS_07	PQ_01	ABCD1234	GH123	0
Dec	Budget	RS_07	PQ_01	CDEEF5678	GH123	22274
Dec	Budget	RS_07	PQ_01	ABCD1234	IH456	0
Dec	Budget	RS_07	PQ_01	CDEEF5678	IH456	0

[patrick_digan · Accepted solution]

@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!

[mbeck]

Patrick_digan,

That is awesome.  Thank you very much!