Putting many columns into one
 

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!

Putting many columns into one
 

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

Putting many columns into one
 

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