Combining multiple length columns to one
 

Hello,

I have the following problem:

I have 4 columns that contain a different number of cells.

How do I combine the cells of those 4 colums to one column, without
having blank cells in (due to the different number of cells per
column)

Thanks.


--
Wingman
------------------------------------------------------------------------
Wingman's Profile: http://www.excelforum.com/member.php...o&userid=33602
View this thread: http://www.excelforum.com/showthread...hreadid=540347

Combining multiple length columns to one
 

Do you mean something like this

=A1 & A2 & A3 & A4

This will set each value in the four cells into one cell. Something like:
A1: Test1
A2: Test2
A3:
A4: Test4

The cell with the formula: Test1Test2Test4

"Wingman" wrote:


Hello,

I have the following problem:

I have 4 columns that contain a different number of cells.

How do I combine the cells of those 4 colums to one column, without
having blank cells in (due to the different number of cells per
column)

Thanks.


--
Wingman
------------------------------------------------------------------------
Wingman's Profile: http://www.excelforum.com/member.php...o&userid=33602
View this thread: http://www.excelforum.com/showthread...hreadid=540347

Combining multiple length columns to one
 

Sorry for being so unprecise,I mean:


A1: 34-4015R B1:34-4017E C1:34-4013E
A2: 59-2780B B2:59-2748B C2:59-2785B
A3: 52-1498B B3:59-0653A
A4: 25-0038C


How do I list all those numbers in Colum D,like:

34-4015R
59-2780B
52-1498B
25-0038C
34-4017E
59-2748B
59-0653A
34-4013E
59-2785B


--
Wingman
------------------------------------------------------------------------
Wingman's Profile: http://www.excelforum.com/member.php...o&userid=33602
View this thread: http://www.excelforum.com/showthread...hreadid=540347

Combining multiple length columns to one
 

You can use Pivot Table with these caveats:
Output will be sorted and
duplicate numbers will be consolidated.
Select: multiple consolidation ranges.
Include a blank row and column to the left and top
of your data when you specify the consolidation range.
Layout: Drag Row and Column field buttons from the diagram
and drag Value into the row field.
Options: Uncheck grand totals

Combining multiple length columns to one
 

Wing

Sub OneColumn()

''''''''''''''''''''''''''''''''''''''''''
'Macro to copy columns of variable length'
'into 1 continous column in a new sheet '
''''''''''''''''''''''''''''''''''''''''''

Dim ilastcol As Long
Dim ilastrow As Long
Dim jlastrow As Long
Dim colndx As Long
Dim ws As Worksheet
Dim myrng As Range
Dim idx As Integer

Set ws = ActiveWorkbook.ActiveSheet
ilastcol = Cells(1, Columns.Count).End(xlToLeft).Column

With Sheets.Add
.Name = "Alldata"
End With

idx = Sheets("Alldata").Index
Sheets(idx + 1).Activate

For colndx = 1 To ilastcol

ilastrow = ws.Cells(Rows.Count, colndx).End(xlUp).Row
jlastrow = Sheets("Alldata").Cells(Rows.Count, 1) _
.End(xlUp).Row

Set myrng = Range(Cells(1, colndx), _
Cells(ilastrow, colndx))
With myrng
.Copy Sheets("Alldata").Cells(jlastrow + 1, 1)
End With
Next

Sheets("Alldata").Rows("1:1").EntireRow.Delete

End Sub


Gord Dibben MS Excel MVP

On Tue, 9 May 2006 13:47:50 -0500, Wingman
wrote:


Sorry for being so unprecise,I mean:


A1: 34-4015R B1:34-4017E C1:34-4013E
A2: 59-2780B B2:59-2748B C2:59-2785B
A3: 52-1498B B3:59-0653A
A4: 25-0038C


How do I list all those numbers in Colum D,like:

34-4015R
59-2780B
52-1498B
25-0038C
34-4017E
59-2748B
59-0653A
34-4013E
59-2785B

Combining multiple length columns to one
 

Or if you prefer formulas...
Insert Name Define
array1 Refers To: =$A$1:$C$4
rowm Refers To: =ROW(INDEX(A:A,1):INDEX(A:A,ROWS(array1)))
colm Refers To: =COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(array1)))
asize Refers To: =ROWS(array1)*COLUMNS(array1)
maxr Refers To: =1000
seque Refers To:
=ROW(INDEX(A:A,asize-COUNTA(array1)+1):INDEX(A:A,asize))
coro Refers To: =SMALL(IF(array1="",0,maxr*colm+rowm),seque)

Select 9 rows (9=counta(array1)) and enter (CSE) this array formula:
=INDEX(array1,RIGHT(coro,LOG(maxr)),INT(coro/maxr))

If you need more than 1000 rows, add zeros to maxr.