Transforming data

[keops]

Hi guys!

I came acroos a problem.

I have data in this form:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1700[/TD]
[/TR]
</tbody>[/TABLE]

I would like to transform them into this form


[TABLE="width: 500"]
<tbody>[TR]
[TD]Region 1[/TD]
[TD]Region 2[/TD]
[TD]Region 3[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]1500[/TD]
[TD]1700[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is there any easy way?

Uros

 

[Akashwani]

Hi Uros, welcome to MrExcel.

This may not be the best solution, but it appears to work!

Excel Workbook
	A	B	C	D	E	F
1	Region	Salary		Region 1	Region 2	Region 3
2	1	1000		1000	2000	3000
3	2	2000		1500	1700
4	3	3000
5	1	1500
6	2	1700
7
8
9
10
11
Sheet1

The formula in D2 needs to be entered with ctrl shift enter NOT just enter.
You can then copy it across and down.
If you put some test data into rows 7:10, you will hopefully see the formula update!!

Good luck.

Ak