Excel 2002: How to realign data table ?

[Mr. Low]

Dear Sir,

May I know if it is possible for me to realign data in Table 1 to Table 2 in
a single step by entering formulas in block A10: C25 ?

At present I do it in a few steps by transposing it first, and entering 3
seperate formulas at A10, B10 and C10 then copy downwards.


Table 1

A B C D E
Code 1 2 3 4
1 K125 200 250 390 410
2 P254 300 360 660 840
3 S141 400 380 910 520
4 T287 500 420 210 180



Table 2

10 K125 1 200
11 K125 2 250
12 K125 3 390
13 K125 4 410
14 P254 1 300
15 P254 2 360
16 P254 3 660
17 P254 4 840
18 S141 1 400
19 S141 2 380
20 S141 3 910
21 S141 4 520
22 T287 1 500
23 T287 2 420
24 T287 3 210
25 T287 4 180


Thanks

Low

 

[Herbert Seidenberg]

Try Pivot Table.
No formulas needed:
http://www.freefilehosting.net/download/3cad0

 

[Jon Peltier]

With the original data in A1:E5, create a pivot table. Drag the Code field
into the Rows area, and one by one drag the fields 1, 2, 3, 4 into the data
area. Copy the pivot table, paste it in another range as values, replace
"Sum of " with "". Select the blank cells in the first pasted column (use
Ctrl+G, Special, Blanks), type = in the formula bar, press the up arrow, and
Press enter, to fill the blanks with the value above. Copy and paste special
values of this range. There's your table.

To do it with formulas, enter this in A10:

=INDEX($A$2:$A$5,(ROW()-ROW($A$10))/4+1)

enter this in B10:

=MOD(ROW()-ROW($A$10),4)+1

and enter this in C10:

=INDEX($B$2:$E$5,(ROW()-ROW($A$10))/4+1,B10)

Then fill these down to row 25.

- Jon

 

[Mr. Low]

Hello Herbert,

Thanks for the tip.

Low

 

[Mr. Low]

Hello Jon,

Thanks for your formulas and detail explanation.

Kind Regards

Low