partial Transpose

[pippop]

Hi all

I am trying to change this table to the next. I can sort of get there with pivot, but wonder if there is a simple method to keep the file flat?

Many thanks[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[/TR]
[TR]
[TD]Area[/TD]
[TD]age[/TD]
[TD]number of people[/TD]
[TD]number of people[/TD]
[TD]number of people[/TD]
[TD]number of people[/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD]1 year old[/TD]
[TD]100[/TD]
[TD]107[/TD]
[TD]108[/TD]
[TD]112
[/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD]2 year old[/TD]
[TD]100[/TD]
[TD]99
[/TD]
[TD]97[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD]3 year old[/TD]
[TD]106[/TD]
[TD]87[/TD]
[TD]87[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD]1 year old[/TD]
[TD]50[/TD]
[TD]49[/TD]
[TD]49[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD]2 year old[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD]3 year old[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]1 year old[/TD]
[TD]2 year old[/TD]
[TD]3 year old[/TD]
[/TR]
[TR]
[TD]AREA[/TD]
[TD]YEAR[/TD]
[TD]number of people[/TD]
[TD]number of people[/TD]
[TD]number of people[/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD]2001[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD]2002[/TD]
[TD]107[/TD]
[TD]99[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD]2003[/TD]
[TD]108[/TD]
[TD]97[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD]2004[/TD]
[TD]112[/TD]
[TD]99[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD]2001[/TD]
[TD]50[/TD]
[TD]39[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD]2002[/TD]
[TD]49[/TD]
[TD]40[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD]2003[/TD]
[TD]49[/TD]
[TD]41[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD]2004[/TD]
[TD]60[/TD]
[TD]42[/TD]
[TD]45[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

Many Thanks

Phil

 

[cyrilbrd]

Given in A1:

Excel 2010
	A	B	C	D	E	F
1			2001	2002	2003	2004
2	Area	age	number of people	number of people	number of people	number of people
3	area1	1 year old	100	107	108	112
4	area1	2 year old	100	99	97	99
5	area1	3 year old	106	87	87	88
6	area2	1 year old	50	49	49	60
7	area2	2 year old	39	40	41	42
8	area2	3 year old	42	43	44	45
9
10
11
12			1 year old	2 year old	3 year old
13	AREA	YEAR	number of people	number of people	number of people
14	area1	2001	100	100	106
15	area1	2002	107	99	87
16	area1	2003	108	97	87
17	area1	2004	112	99	88
18	area2	2001	50	39	42
19	area2	2002	49	40	43
20	area2	2003	49	41	44
21	area2	2004	60	42	45
Sheet4

Formula in C14 is =INDEX($C$3:$F$8,MATCH(1,INDEX(($B$3:$B$8=C$12)*($A$3:$A$8=$A14),0),0),MATCH($B14,$C$1:$F$1,0)) copied right and down till needed.

Would that work for you?

 

[pippop]

Thanks cyrilbrd

This looks very promising. Thanks for quick reply. It certainly does what I am looking for. I just need to work out a way for changing the Columns A and B (I have 91 age variables and 365 areas to deal with making over 33,000 rows)

I will report back soon.

Thanks again

 

[cyrilbrd]

Thanks cyrilbrd

This looks very promising. Thanks for quick reply. It certainly does what I am looking for. I just need to work out a way for changing the Columns A and B (I have 91 age variables and 365 areas to deal with making over 33,000 rows)

I will report back soon.

Thanks again

Noted. Most welcome.