How to re-arrange Excel database from 1 long row, into 3 short rows and automatically repeat the process

microsoft excelmicrosoft-excel-2010vba

I would appreciate help on the above-mentioned topic.
I am unfamiliar with Visual Basic for Excel, so will need step-by-step guidance (if solution is via Visual Basic).

For example :-
Row 1, Sheet A:

A1  B1  C1   D1  E1  F1  G1  H1   I1

To be re-arranged into Sheet B : 

Row 1 :  A1,  B1,  C1
Row 2 :  D1,  E1,  F1
Row 3 :  G1,  H1,  I1

The Sheet A (database sheet) has a lot of rows (example 3,000 rows), hence the Sheet B is estimated to have 9,000 rows (i.e. 3 x 3,000).

Thanking you in anticipation of your speedy response.

Best Answer

The solution to this lies in using the excel Row/Column and Indirect sheet function.

if the original data is in sheet1, then in sheet 2 paste the following formula:

=INDIRECT("Sheet1!"&"R"&CEILING(ROW(Sheet1!A1)/3,1)&"C"&MOD(3*(ROW(Sheet1!A1)-1)+ COLUMN(Sheet1!A1)-1,9)+1,FALSE)

and then drag the formula across and down

Related Question