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

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.

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

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

"pfdino" wrote in message
...
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.

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

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

On Mon, 19 Mar 2007 12:38:07 -0700, pfdino
wrote:

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.