Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
how do i arrange columns by its numerical position with a click | Excel Discussion (Misc queries) | |||
how to split data into columns and arrange the resulting data | Excel Discussion (Misc queries) | |||
arrange columns alphabetically in Excel | New Users to Excel |