Data formatting help

[EMY245]

Hi,

I have a report that i download from an internal system (weekly). It lists all employees and details their compensation. However, it lists each compensation element in a seperate row - example below.

I need to be able to covert this so that one employee only takes up one row and the compensation elements are listed seperately as columns.

Does anyone have any ideas or suggestions on the best way to achieve this? I need to be able to use a system that can be refreshed weekly or each time an updated report is run.

Appreciate any help!
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee ID[/TD]
[TD]Employee Name[/TD]
[TD]Compensation[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]J Bloggs[/TD]
[TD]Base salary[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]J Bloggs[/TD]
[TD]Car allowance[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]J Bloggs[/TD]
[TD]Housing allowance[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]234555[/TD]
[TD]B Smith[/TD]
[TD]Base salary[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]234555[/TD]
[TD]B Smith[/TD]
[TD]Car allowance[/TD]
[TD]xx[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

try this

F2 and copy down to find the unique ID
G2 and copy down for the corresponding name
H2 copy across to J2 (increase for more compensations) and down
K2 copy across and down for the amount

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M
Employee ID	Employee Name	Compensation	Amount	Employee ID	Employee Name	Compensation	Amount
J Bloggs	Base salary	J Bloggs	Base salary	Car allowance	Housing allowance
J Bloggs	Car allowance	B Smith	Base salary	Car allowance
J Bloggs	Housing allowance
B Smith	Base salary
B Smith	Car allowance

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"]234555[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]234555[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]234555[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=VLOOKUP(F2,A:B,2,0)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K2[/TH]
[TD="align: left"]=SUMIFS($D$2:$D$6,$A$2:$A$6,$F2,$C$2:$C$6,H2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=INDEX($A$2:$A$6, MATCH(0, COUNTIF($F$1:F1, $A$2:$A$6), 0))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]{=IFERROR(INDEX($C$2:$C$6,SMALL(IF($A$2:$A$6=$F2,ROW($A$2:$A$6)-ROW($F$2)+1),COLUMNS($E2:E2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 922"]
<colgroup><col span="2"><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Employee ID[/TD]
[TD]Employee Name[/TD]
[TD]Compensation[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123456[/TD]
[TD]J Bloggs[/TD]
[TD]Base salary[/TD]
[TD]xx1[/TD]
[TD]Base salary xx1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123456[/TD]
[TD]J Bloggs[/TD]
[TD]Car allowance[/TD]
[TD]xx2[/TD]
[TD]Base salary xx1 Car allowance xx2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123456[/TD]
[TD]J Bloggs[/TD]
[TD]Housing allowance[/TD]
[TD]xx3[/TD]
[TD]Base salary xx1 Car allowance xx2 Housing allowance xx3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]234555[/TD]
[TD]B Smith[/TD]
[TD]Base salary[/TD]
[TD]xx4[/TD]
[TD]Base salary xx4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]234555[/TD]
[TD]B Smith[/TD]
[TD]Car allowance[/TD]
[TD]xx5[/TD]
[TD]Base salary xx4 Car allowance xx5[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]easy to use concatenate and count employee id changes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="colspan: 2"]Base salary xx1 Car allowance xx2 Housing allowance xx3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="colspan: 2"]Base salary xx4 Car allowance xx5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]then use offset match to pull out the full details as above[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]if this approach ok I will post formulas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]