Excel 2002: Any quick way of transposing data ?

[Guest]

Dear Sir,

Lets consider the following worksheet.

Debtor Analysis
A B C D E F
1 H364 CGS LTD AMT DUE 396,709
2 H364 CGS LTD CLAIMS 131,686
3 H364 CGS LTD PAYABLE 37,853
4 H364 CGS LTD DISPUTE 38,787
5 H364 CGS LTD FAF 1,095
6 H364 CGS LTD REBATES 187,288
7 M006 DGF CO AMT DUE 206,150
8 M006 DGF CO CLAIMS 489
9 M006 DGF CO PAYABLE 156,925
10 M006 DGF CO DISPUTE -
11 M006 DGF CO FAF -
12 M006 DGF CO REBATES 48,735
13 T268 DK PLC AMT DUE 36,933
14 T268 DK PLC CLAIMS 300
15 T268 DK PLC PAYABLE -
16 T268 DK PLC DISPUTE 4,618
17 T268 DK PLC FAF -
18 T268 DK PLC REBATES 32,015


Transposed Data
21 H364 H364 H364 H364 H364 H364
22 CGS LTD CGS LTD CGS LTD CGS LTD CGS LTD CGS LTD
23 AMT DUE CLAIMS PAYABLE DISPUTE FAF REBATES
24 396,709 131,686 37,853 38,787 1,095 187,288
25 M006 M006 M006 M006 M006 M006
26 DGF CO DGF CO DGF CO DGF CO DGF CO DGF CO
27 AMT DUE CLAIMS PAYABLE DISPUTE FAF REBATES
28 206,150 489 156,925 - - 48,735
29 T268 T268 T268 T268 T268 T268
30 DK PLC DK PLC DK PLC DK PLC DK PLC DK PLC
31 AMT DUE CLAIMS PAYABLE DISPUTE FAF REBATES
32 36,933 300 - 4,618 - 32,015


I wish to transpose A$1: D$18 into the layout as in A$21:F$32 as illustrated
above.

At present I could copy and transpose one block at a time using paste
special > transpose function. Copying and transposing A$118 at one go
using paste special could only produce one block of data horizontally.

May I know Is there any quick way of doing it using Excel formulas?

What formula must I enter at A21 and copy sideway and downwards to get the
transposed data as illustrated?


Thanks

Low

 

[Guest]

try:

=OFFSET($A$1,INT((ROWS($A$21:$A21)-1)/4)*6+COLUMN()-1,MOD(ROW()-1,4))