VBA to rearrange 11000 records into 550 rows (20 records combined into a single row)

Book1 and Book2 are workbooks that I have modified in order to protect private information.

Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).

Can somebody offer a macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
Book1.xls
Book2.xls

Hi,
Like this?

Data in sheet1, Output in sheet 2 :

Please Login or Register  to view this content.

Those are variant.
By default is you indicate nothing the variables are dimmed as variant.

Hello,

Got your PM, so here are some explanations...

Not sure how to explain it well..

I’m using 2 arrays : ar contains the data, arF the final data.

First I need to determine the size (rows and columns) of the arF array. For the rows, I keep the same number of row of the data array (even though I know it will be smaller). For the columns, you have 6 columns in your data array and want to merge 20 rows so that’s why I multiply Ubound(ar, 2) * 6. Where Ubound(ar, 2) = 6

Next step is to create the header. The first row of the data array must be copied 20 times. Column A (or 1) will be copied 20 times in column 1, 7, 13, 20, 26, etc… of the final array.
Make a table to see the relationship :
For j = 1
N position (column)
1 1
2 7
3 13
4 20

For j = 2
N position (column)
1 2
2 8
3 14
4 21

From there, you can find that
Column = (n-1) * 6 +j
Or
Column = (n-1) * ubound(ar, 2) + j


Same relationship for the data, except that we increment the row (k) of the final array when we have copied 20 rows of the data array.

Final step is to resize the final array with “k” rows.

Hope this helps…

You can also do this with a formula on sheet 2:
=OFFSET(Sheet1!$A$1,(ROW()-1)*20+INT((COLUMN()-1)/6)+1,MOD(COLUMN()-1,6))
Simply copy across 119 columns to DP1, then down as far as needed (to row 550 for 11,000 rows of data).