Transform excel data into certain format

Nuruddin

Hi All,

Below is my table

		a1	a2	a3
	DESCRIPTION	a	a	a
		l	l	sb
1010	Merchandise Sales	56864596.79	0	61924583.15
1011	Merchandise Sales - Adjustment	0	64534.67	0
1020	Royalty Income	0	0	0
1030	Service Income	0	0	0
1040	Variable Interest Entity Sales	0	0	0

I want it to be like

4493B	SUBURBIA	l	4101010	Merchandise Sales	56864596.79
4493B	SUBURBIA	l	4101011	Merchandise Sales - Adjustment	0
4493B	SUBURBIA	l	4101020	Royalty Income	0
4493B	SUBURBIA	l	4101030	Service Income	0
4493B	SUBURBIA	l	4101040	Variable Interest Entity Sales	0
4493B	SAWSA	l	4101010	Merchandise Sales	0
4493B	SAWSA	l	4101011	Merchandise Sales - Adjustment	64534.67
4493B	SAWSA	l	4101020	Royalty Income	0

Please help me... thank you 🙂

FFFool

See attached. You will likely need some tweaks to make it work for a larger data set, though. 

Data Manipulation Example.yxmd