How to re-arrange Excel database from 1 long row, into 3 short rows of unequal lengths and automatically repeat the process?
- by user326884
This question is an extension/continuation of my previous question at How to re-arrange Excel database from 1 long row, into 3 short rows and automatically repeat the process? which was answered by Jason Lewis of which I'm grateful. But being a dummy in "Indirect' Excel function, I need assistance again :
For example :- In Sheet A, Row 1 has the following data in each cell (all together 72 cells occupied):
A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 N1 O1 P1 Q1
R1 S1 T1 U1 V1 W1 X1 Y1 Z1 AA1 AB1 AC1 AD1 AE1 AF1 AG1
AH1 AI1 AJ1 AK1 AL1 AM1 AN1 AO1 AP1 AQ1 AR1 AS1 AT1 AU1
AV1 AW1 AX1 AY1 AZ1 BA1 BB1 BC1 BD1 BE1 BF1 BG1 BH1 BI1
BJ1 BK1 BL1 BM1 BN1 BO1 BP1 BQ1 BR1 BS1 BT1
To be re-arranged into Sheet B in the following format:
Row 1 : A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 N1 O1
P1 Q1 R1 S1 T1 U1 V1 W1 X1 Y1 Z1 AA1 AB1 AC1 AD1
AE1 AF1 AG1 AH1 AI1
Row 2 : AJ1 AK1 AL1 AM1 AN1 AO1 AP1 AQ1 AR1 AS1 AT1 AU1
AV1 AW1 AX1 AY1 AZ1 BA1 BB1 BC1 BD1 BE1 BF1 BG1 BH1 BI1
BJ1 BK1
Row 3 : BL1 BM1 BN1 BO1 BP1 BQ1 BR1 BS1 BT1
The Sheet A (database sheet) has a lot of rows (example 3,000 rows, each rows has 72 cells occupied with data), hence the Sheet B (reformatted database) is estimated to have 9,000 rows (i.e. 3 x 3,000) of unequal lengths.
Thanking you in anticipation of your speedy response.