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 · Accepted solution]

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

[RobertOdera]

HI, @Nuruddin 

Looks like you're changing your source sample files?

Kindly consider a new post or reply with that captures iteration in requirements, versus editing an already solved prior post 🙂

Now your desired output has data that is not present in your source.

@FFFool  solution or approach should still work.

If, however, you're running into an issue where your source file has no Headers, and you can't edit the filed to add a Header row, then try the below.

The first part is the treatment to auto-add Headers versus typing them in (when you can't type them in).

The second part is the @FFFool  solution flow.

Kindly credit @FFFool

Cheers!