Formula help-copy every 100th cell

[Dumptruck]

I have a very large data block (~20,000 rows, single column). I would like to copy every 100th cell over to the next column without any spaces between them (i.e. the end result would be ~200 values with no spaces in-between).

Thanks.

 

[Lewiy]

Try this macro

Sub HundredthCell()
Dim iStart As Long
Dim i As Long
iStart = Val(InputBox("Please type the number of the first row"))
i = 1
Do
    Cells(i, 2) = Cells(iStart, 1)
    i = i + 1
    iStart = iStart + 100
Loop Until Cells(iStart, 1) = ""
End Sub

 

[Jonmo1]

Welcome to the board...

Assuming Data in Column A, results going to column B.
this will get
a1
a101
a201

In B1
=INDEX(A:A,100*(Row(A1)-1)+1)
fill down

Change the Bold Red 1 to determine the Starting Row #.

Hope that helps..

 

[Dumptruck]

Worked great jonmo1, thanks for the help.

 

[Aladin Akyurek]

If you want it to be robust...

The data to copy in A1:A10000 and the copy starts in B1...

=INDEX($A$1:$A$10000,(ROWS($B$1:B1)-1)*100)