Complex crosstable conversion

[#1]

Hi all,

I needed some help in converting a payslip into a linear table. Currently this is the table I have in Excel:

160897	J Bloggs		Dep  1233 R979
Net	1199.68		Run date 23/11/12 (21/10/12 - 17/11/12)
Basic	160.0000		@ 8.8400	1414.4
Basic@1.5	2.0000		@ 13.2600	26.52
TOTAL GROSS PAY		1477.36
Gross YTD	13190.86		Prev emp pay	0
Taxable YTD	13190.86		Prev emp tax	0
Tax YTD	1515.2
WPBP - LA pay	13190.86
160898	A Cloggs		Dep  1233 R979
Net	2323.23		Run date 23/11/12 (21/10/12 - 17/11/12)
Basic	160.0000		@ 8.8400	1213.4
Basic@1.5	2.0000		@ 13.2600	26.52
TOTAL GROSS PAY		1477.36
Gross YTD	13190.86		Prev emp pay	0
Taxable YTD	13190.86		Prev emp tax	0
Tax YTD	1515.2
WPBP - LA pay	13190.86
160899	M Bobbs		Dep  1233 R979
Net	4535.76		Run date 23/11/12 (21/10/12 - 17/11/12)
Basic	160.0000		@ 8.8400	1414.4
Basic@1.5	2.0000		@ 13.2600	5345.8
TOTAL GROSS PAY		1477.36
Gross YTD	13190.86		Prev emp pay	0
Taxable YTD	13190.86		Prev emp tax	0
Tax YTD	1515.2
WPBP - LA pay	13190.86

I want to convert it into something like this:

Net	Basic	@ 8.8400	Basic@1.5	@ 13.2600	Gross YTD	Tax YTD	WPBP - LA pay	Prev emp pay	Prev emp tax	TOTAL GROSS PAY
160897	J Bloggs	1199.68	160.0000	1414.4	2.0000	26.52	13190.86	1515.2	13190.86	0	0	1477.36
160898	A Cloggs	2323.23	160.0000	1213.4	2.0000	26.52	13190.86	1515.2	13190.86	0	0	1477.36
160899	M Bobbs	4535.76	160.0000	5345.8	2.0000	26.52	13190.86	1515.2	13190.86	0	0	1477.36

Message was edited by: sabah dada

[#2]

Muhammad,

Please see attached the format that I want

[arsal_90]

Hi sabah

I try my best to do for your solution. Please see in attached file is this your desired values

[arsal_90]

Tell me is this slip data only available in excel sheet?  is there no database maintain for it. if there is database tables maintain for it then do work on that

[#5]

Hi Muhammad,

Many thanks. This is what I am looking for. Could you kindly put a copy of the Excel file that you used for this so I can see the set up?

Thank you for your help.

[arsal_90 · Accepted solution]

Hi Sabah

I used the excel that you provided all i do some thing in the Transformation. So sabah can u please refer this post as Answer for other people help. And if u have any other query then let me know.

Thanks & regards

Muhammad