Changing the format for SAP upload

[Boboka]

Employee No (A1 Cell in Excel)	Electricity (B1 cell in Excel)	Water (C1 Cell in excel)	Gas (D1 cell in Excel)	Meal (E1 cell in Excel)	Rental (F1 cell in Excel)
1005511	26.12	11.52	0	144	45
1065842	33.28	41.97	9.64	0	10
1053265	30.82	18	0	144	45

Hi All,

I would like to ask if I got above master data, about 2000 rows. How can I make into below this format for SAP upload? Assuming the header start from A1 Cell. If transpose above master data from row to column, will it be better?
5560 = electricity expense
5562 = water expenses
5561 = Gas expenses
5104 = Meal expenses
5130 = rental expense

if any expenses is 0 amount, we can omit it. Example as below for 1005511 employees, he did not have gas expenses, so it is not shown below, for 1065842 employees, he did not have meal expenses, so it is not show below. I can't achieve below format using vlookup.

Thank in advance for any expert advice.

Employee No	WGTYP	AMOUNT
1005511	5560	26.12
1005511	5562	11.52
1005511	5104	144
1005511	5130	45
1065842	5560	33.28
1065842	5562	41.97
1065842	5561	9.64
1065842	5130	10

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Employee No	Electricity	Water	Gas	Meal	Rental
2	1005511	26.12	11.52	0	144	45		1005511	Electricity	26.12
3	1065842	33.28	41.97	9.64	0	10		1005511	Water	11.52
4	1053265	30.82	18	0	144	45		1005511	Meal	144
5								1005511	Rental	45
6								1065842	Electricity	33.28
7								1065842	Water	41.97
8								1065842	Gas	9.64
9								1065842	Rental	10
10								1053265	Electricity	30.82
11								1053265	Water	18
12								1053265	Meal	144
13								1053265	Rental	45
Sheet5

Cell Formulas
Range	Formula
H2:J13	H2	=HSTACK(TOCOL(IF(B2:F4=0,1/0,A2:A4),2),TOCOL(IF(B2:F4=0,1/0,B1:F1),2),TOCOL(IF(B2:F4=0,1/0,B2:F4),2))
Dynamic array formulas.

 

[Boboka]

Hi Fluff.

This work amazing. Just to check on the if statement (B2:F4=0, 1/0, A2:A4). What is the 1/0 mean in the if statement if it is true.

Thanks a lot.

 

[Fluff]

That will return a #DIV/0! error which is then removed by using 2 as the 2nd argument in TOCOL