Divide Every 40 Numbers into Columns wise

[Mohanmoni]

Hi,

I am looking for formula that divides in data or numbers into 40 rows and paste in new column.

Example:
============
Column A
15248
2113547
2665478
265475
2546981
.
.
.
Etc

Like above example in Column"A" I have a list of numbers upto 200,000 (2 Lakh). Now I need this to divide after every 40 rows and paste in new column.

Example:
=============
Column A
15248
2113547
2665478
265475
2546981
.
.
First 40 numbers in Column "A" and Next 40 numbers in Column "B" and Next 40 numbers in Column"C"..........

Please suggest have any code for this.

Thanks You,
Mohan

 

[alansidman]

Option Explicit


Sub mohan()
    Dim i As Long, lc As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lc = 1
    For i = 41 To lr Step 40
        Range("A" & i & ":A" & i + 39).Cut Cells(1, lc + 1)
        lc = lc + 1
    Next i
End Sub

 

[FormR]

I am looking for formula that divides in data or numbers into 40 rows and paste in new column.

Hi, here is a formula you can use; assumes you data in column A starts on row 2 and your results start in B2 - the formula can be copied down 40 rows and across as far as is required.

=INDEX($A$2:$A$200000,ROWS(B$2:B2)+(40*(COLUMNS($B2:B2)-1)))

 

[Mohanmoni]

Thank You Alansidman.

 

[footoo]

This should be quicker :

Sub FortyRows()
Dim lr#, sRay, dRay, r#
lr = Cells(Rows.Count, "A").End(xlUp).Row
sRay = Range("A1:A" & lr)
ReDim dRay(1 To 40, 1 To Int(lr / 40) + 1)
For r = 0 To lr - 1
  dRay(1 + (r Mod 40), 1 + Int(r / 40)) = sRay(r + 1, 1)
Next
Application.ScreenUpdating = False
[A1].Resize(40, UBound(dRay, 2)) = dRay
Range("A41:A" & lr).ClearContents
Application.ScreenUpdating = True
End Sub