Columns to Rows (bit complex)

[wsurfa]

Hi all,

I need to convert a payroll file from the first layout below, to the second layout with one row per Person. There are about 30 different payments that indivduals can receive, and there is no pattern to who gets what. So the final sheet should have one row per person, with Number, Name and then 30 further columns headed by the payment name.




<SCRIPT language=JavaScript src="<a href=" popup.js" colo puremis sun www.interq.or.jp http:></SCRIPT><CENTER><TABLE" target="_blank"><CENTER>
</CENTER></CENTER>

 

[wsurfa]

Hi all,

I need to convert a payroll file from the first layout below, to the second layout with one row per Person. There are about 30 different payments that indivduals can receive, and there is no pattern to who gets what. So the final sheet should have one row per person, with Number, Name and then 30 further columns headed by the payment name.<SCRIPT language=JavaScript src="<a href=" target="_blank" href="http://www.interq.or.jp" popup.js" colo puremis sun www.interq.or.jp http:></SCRIPT>

January 09_Payroll.xls
	A	B	C	D
1	NColaborador	Evento	Datadoprocessamento	ValorIlquido
2	17	PersonA		0
3	0	R001-Vencimento		1405
4	0		31/01/2009	1405
5	0	R005-Sub.Refeio		110.04
6	0		31/01/2009	110.04
7	0	R041-PrmioMBO		1820
8	0		31/01/2009	1820
9	0	F006-Doena/Mdico(Hr)		0
10	0		31/01/2009	0
11	0	P034-Restaurante		36
12	0		31/01/2009	36
13	23	PersonB		0
14	0	R001-Vencimento		1681
15	0		31/01/2009	1681
16	0	R005-Sub.Refeio		110.04
17	0		31/01/2009	110.04
18	0	R041-PrmioMBO		2100
19	0		31/01/2009	2100
20	0	P034-Restaurante		36
21	0		31/01/2009	36
Grelha

<CENTER></CENTER>

 

[ColinKJ]

Hi,

Can you confirm in the original layout you are only using Columns A - D, and will it be a regular routine, or just a one off.

Regards

ColinKJ

 

[wsurfa]

Hi Colin,

Yes in the original layout only columns A-D are used.

It is likely to be a regular requirement for the next year (so will be run 12 times or so)

thanks for the help

Iain

 

[ColinKJ]

Hi Iain,

I'm going to be out and about today (Wed), not sure what time zone you're in, I'm in Spain GMT+1. I should be able to get back to you tomorrow if ok with you.

Can you send me a private messaage with an email address, and I'll send it to you when complete.

Regards

Colin

 

[wsurfa]

Thanks Colin - PM sent

Iain

 

[wsurfa]

Thanks to Colin for the solution he sent me. Worked brilliantly

Iain