Combining multiple length columns to one

Wingman

unread,

May 9, 2006, 12:22:48 PM5/9/06

to

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?action=getinfo&userid=33602
View this thread: http://www.excelforum.com/showthread.php?threadid=540347

Abode

unread,

May 9, 2006, 1:14:01 PM5/9/06

to

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

unread,

May 9, 2006, 2:47:50 PM5/9/06

to

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

Herbert Seidenberg

unread,

May 9, 2006, 3:44:02 PM5/9/06

to

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

Gord Dibben

unread,

May 9, 2006, 3:59:51 PM5/9/06

to

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

Herbert Seidenberg

unread,

May 9, 2006, 9:04:09 PM5/9/06

to

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.