How to combine multiple columns into different cells in one column

[Blake]

I have data in different columns starting with column A going down row 1 thru
14. So column B row 1 thru 14, column C 1 thru 14, etc.

I need to consolidate everything from column a1-a14 to agu1-agu14 into one
column, for example:

a1
a2
a3
..
..
..
a14
b1
b2
b3
..
..
..
b14

etc.

How do I go about doing this?

I tried doing transpose and that didn't work.

I'm using Excel 2007 if that makes a difference.

Thanks
Blake

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim HowManyToCopy As Long
Dim RngToCopy As Range
Dim DestCell As Range

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

HowManyToCopy = 14

With NewWks
Set DestCell = .Range("A1")
End With

With CurWks
FirstCol = 1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'or if row 1 may not always be used...
LastCol = .Range("AUG1").Column

FirstRow = 1

For iCol = FirstCol To LastCol
Set RngToCopy = .Cells(FirstRow, iCol).Resize(HowManyToCopy)
RngToCopy.Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(RngToCopy.Rows.Count)
Next iCol
End With
End Sub

 

[jobowo]

I have data in different columns starting with column A going down row 1 thru
14. So column B row 1 thru 14, column C 1 thru 14, etc.

I need to consolidate everything from column a1-a14 to agu1-agu14 i
I tried doing transpose and that didn't work.

Blake, what do you mean by "consolidate"? Do you mean add the contents of
each row across? Do you have numbers in your cells and you want to add them,
or text and you want to concatenate them? The answer if they are numbers is
to use SUM(A1:AGU14) and if you have text you can concatenate them (A1 & B1
etc good luck with that!)

 

[MyVeryOwnSelf]

I have data in different columns starting with column A going down row

1 thru 14. So column B row 1 thru 14, column C 1 thru 14, etc.

I need to consolidate everything from column a1-a14 to agu1-agu14 into
one column, for example:

a1
a2
a3
.
.
.
a14
b1
b2
b3
.
.
.
b14

etc.

Here's one way with Excel 2003.

If the data is in Sheet 1, put this formula in Sheet2!A1 and copy down as
far as needed:
=OFFSET(Sheet1!$A$1,MOD(ROW()-1,14),INT((ROW()-1)/14))

If some cells in Sheet1 are empty, you'll see zeros for them in Sheet2.
If this is a problem, try
=IF(<above formula>="", "", <above formula>)

 

[Teethless mama]

=INDEX($A$1:$AGU$14,MOD(ROWS($1:43)-1,14)+1,CEILING(ROWS($1:43)/14,1))

copy down as far as needed

 

[forceforever]

Teethless mama wrote on 10/20/2009 00:51 ET :

=INDEX($A$1:$AGU$14,MOD(ROWS($1:43)-1,14)+1,CEILING(ROWS($1:43)/14,1))

copy down as far as needed

What does 43 stand for in Blake's Index formula? I tried in the followin
example:
a 1
b 2
c 3
d 4
e 5

although it worked but it kept putting either e or 5 on the first cell. Did
do something wrong? Any help is greatly appreciated.

Also I tried the offset formula but it keeps producing zeros in some spots.
tried the IF formula with no success because Excel 2007 does not want to accep
the equal sign. What do you put in between these "","" in the formula.

Thanks,