Hi everybody, yet another Excel question from me. :)

I was looking through the knowledge base to try and find how to combine data
from multiple columns into one on a new sheet, so

a b c
a b
a

would become

a
a
a
b
b
c

I found this formula, and it definitely works for 30 rows in each column, as
the person who asked the original question wanted, but is there any way to
modify this formula so columns with varying lengths will still work without
zeros in between them? I've never seen these functions before, so I don't
know how to modify it to make it work. My column lengths are currently
between 2700 and 3400 rows, but they'll probably get longer, and I don't want
to have to cut and paste all of them. The columns go from A to AZ, and if I
keep all the zeros in there it will overflow the maximum row limit for
worksheets, plus it'll be harder to work with.

=OFFSET(INDIRECT("Sheet1!"&CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30)+65)&"1"),MOD(ROWS(Sheet1!$A$1:A1)-1,30),)

Thanks so much!

One other thing that I forgot before, is there a way to do this with the
formula I'm asking about if the columns all don't start in the same row, say
one starts in A32 and one starts in A11?
Thanks!

"jezzica85" wrote:

Hi everybody, yet another Excel question from me. :)

I was looking through the knowledge base to try and find how to combine data
from multiple columns into one on a new sheet, so

a b c
a b
a

would become

a
a
a
b
b
c

I found this formula, and it definitely works for 30 rows in each column, as
the person who asked the original question wanted, but is there any way to
modify this formula so columns with varying lengths will still work without
zeros in between them? I've never seen these functions before, so I don't
know how to modify it to make it work. My column lengths are currently
between 2700 and 3400 rows, but they'll probably get longer, and I don't want
to have to cut and paste all of them. The columns go from A to AZ, and if I
keep all the zeros in there it will overflow the maximum row limit for
worksheets, plus it'll be harder to work with.

=OFFSET(INDIRECT("Sheet1!"&CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30)+65)&"1"),MOD(ROWS(Sheet1!$A$1:A1)-1,30),)

Thanks so much!

I know I couldn't do this with formulas, but it wouldn't be so difficult with a
small macro.

If you want to try:

Option Explicit
Sub testme01()
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim DestCell As Range
Dim FWks As Worksheet
Dim TWks As Worksheet
Dim TopCell As Range
Dim BotCell As Range

Set FWks = Worksheets("sheet1")
Set TWks = Worksheets.Add
Set DestCell = TWks.Range("a1")

With FWks
With .UsedRange
FirstCol = .Column
LastCol = .Columns(.Columns.Count).Column
End With

For iCol = FirstCol To LastCol
If Application.CountA(.Cells(1, iCol).EntireColumn) = 0 Then
'do nothing, no data
Else
Set TopCell = .Cells(1, iCol)
If IsEmpty(TopCell.Value) Then
Set TopCell = .Cells(2, iCol)
If IsEmpty(TopCell.Value) Then
Set TopCell = .Cells(1, iCol).End(xlDown)
End If
End If
Set BotCell = .Cells(.Rows.Count, iCol).End(xlUp)
.Range(TopCell, BotCell).Copy _
Destination:=DestCell
With TWks
Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End If
Next iCol
End With

'if you had embedded empty cells in that range and want to
'keep them in the output sheet, then delete this next section
'if you want those embedded empty cells kept, then keep this section
On Error Resume Next
With TWks
.Range("a1").EntireColumn.Cells _
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
On Error GoTo 0
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

jezzica85 wrote:

One other thing that I forgot before, is there a way to do this with the
formula I'm asking about if the columns all don't start in the same row, say
one starts in A32 and one starts in A11?
Thanks!

"jezzica85" wrote:

Hi everybody, yet another Excel question from me. :)

I was looking through the knowledge base to try and find how to combine data
from multiple columns into one on a new sheet, so

a b c
a b
a

would become

a
a
a
b
b
c

I found this formula, and it definitely works for 30 rows in each column, as
the person who asked the original question wanted, but is there any way to
modify this formula so columns with varying lengths will still work without
zeros in between them? I've never seen these functions before, so I don't
know how to modify it to make it work. My column lengths are currently
between 2700 and 3400 rows, but they'll probably get longer, and I don't want
to have to cut and paste all of them. The columns go from A to AZ, and if I
keep all the zeros in there it will overflow the maximum row limit for
worksheets, plus it'll be harder to work with.

=OFFSET(INDIRECT("Sheet1!"&CHAR(INT((ROWS(Sheet1!$ A$1:A1)-1)/30)+65)&"1"),MOD(ROWS(Sheet1!$A$1:A1)-1,30),)

Thanks so much!


--

Dave Peterson