Arrange data spanning 8 columns and 3 rows to 24 columns and 1 row

[Guest]

I know the transpose option under Paste Special menu will arrange data from
horizontal to vertical and vice versa. Is there a function or formula that
will re-arrange data from multiple columns/rows into one row? For example,
from 8 columns across and 3 rows down to 24 columns across and 1 row down?
(I have 1000 rows of data, so manually would be a time-consuming process).
Thank you.

 

[David Biddulph]

=OFFSET($A$1,INT((COLUMN(A1)-1)/8),MOD(COLUMN(A1)-1,8))

 

[Gord Dibben]

Assuming you want a1:H1 and A2:H2 and A3:H3 copied across 24 columns use this
macro.

Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(1, 8).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 1, "A").Resize(1, 8).Cut _
Destination:=Cells(iTarget, "I")
Cells(iSource + 2, "A").Resize(1, 8).Cut _
Destination:=Cells(iTarget, "Q")

iSource = iSource + 3
iTarget = iTarget + 1
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub


Gord Dibben MS Excel MVP