Putting many columns into one

[jezzica85]

Hi all,
Yet another Excel question from me, but I know I can turn here, everybody's
always so helpful, thanks again! My question this time is, is there an easy
way to consolidate colums without having to cut and paste them all together.
Basically, is there a quick way to turn:

a b c d
a b c d
a b c d
a b c d

into:
a
a
a
a
b
b
b
b
c
c
c
c
d
d
d
d

Thanks a million!

[Bob Phillips]

Some VBA

Sub Test()
Dim iLastRow As Long
Dim iLastCol As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
If iLastCol > 1 Then
Rows(i + 1).Resize(iLastCol - 1).Insert
For j = 2 To iLastCol
Cells(i + j - 1, "A").Value = Cells(i, j).Value
Next j
Cells(i, 2).Resize(, iLastCol - 1).Clear
End If
Next i

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jezzica85" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
> Yet another Excel question from me, but I know I can turn here,
everybody's
> always so helpful, thanks again! My question this time is, is there an
easy
> way to consolidate colums without having to cut and paste them all
together.
> Basically, is there a quick way to turn:
>
> a b c d
> a b c d
> a b c d
> a b c d
>
> into:
> a
> a
> a
> a
> b
> b
> b
> b
> c
> c
> c
> c
> d
> d
> d
> d
>
> Thanks a million!

[Ken Wright]

Assuming your data is in A1:D100, then in say H5 (Row is important - column
is not) put the following and copy down to H505

=OFFSET($A$1,FLOOR((ROW()-5)/4,1),MOD(ROW()-5,4))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"jezzica85" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
> Yet another Excel question from me, but I know I can turn here,
> everybody's
> always so helpful, thanks again! My question this time is, is there an
> easy
> way to consolidate colums without having to cut and paste them all
> together.
> Basically, is there a quick way to turn:
>
> a b c d
> a b c d
> a b c d
> a b c d
>
> into:
> a
> a
> a
> a
> b
> b
> b
> b
> c
> c
> c
> c
> d
> d
> d
> d
>
> Thanks a million!