software

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: www.excelforum.com/member.php...oamp;userid=33602
View this thread: www.excelforum.com/showthread...hreadid=540347Do you mean something like this

=A1 amp; A2 amp; A3 amp; 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

quot;Wingmanquot; wrote:

gt;
gt; Hello,
gt;
gt; I have the following problem:
gt;
gt; I have 4 columns that contain a different number of cells.
gt;
gt; How do I combine the cells of those 4 colums to one column, without
gt; having blank cells in (due to the different number of cells per
gt; column)
gt;
gt; Thanks.
gt;
gt;
gt; --
gt; Wingman
gt; ------------------------------------------------------------------------
gt; Wingman's Profile: www.excelforum.com/member.php...oamp;userid=33602
gt; View this thread: www.excelforum.com/showthread...hreadid=540347
gt;
gt;


Sorry for being so unprecise,I mean:A1: 34-4015RB1:34-4017EC1:34-4013E
A2: 59-2780BB2:59-2748BC2:59-2785B
A3: 52-1498BB3:59-0653A
A4: 25-0038CHow 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: www.excelforum.com/member.php...oamp;userid=33602
View this thread: www.excelforum.com/showthread...hreadid=540347You 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 totalsWing

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 = quot;Alldataquot;
End With

idx = Sheets(quot;Alldataquot;).Index
Sheets(idx 1).Activate

For colndx = 1 To ilastcol

ilastrow = ws.Cells(Rows.Count, colndx).End(xlUp).Row
jlastrow = Sheets(quot;Alldataquot;).Cells(Rows.Count, 1) _
.End(xlUp).Row

Set myrng = Range(Cells(1, colndx), _
Cells(ilastrow, colndx))
With myrng
.Copy Sheets(quot;Alldataquot;).Cells(jlastrow 1, 1)
End With
Next

Sheets(quot;Alldataquot;).Rows(quot;1:1quot;).EntireRow.Delete

End SubGord Dibben MS Excel MVP

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

gt;
gt;Sorry for being so unprecise,I mean:
gt;
gt;
gt;A1: 34-4015RB1:34-4017EC1:34-4013E
gt;A2: 59-2780BB2:59-2748BC2:59-2785B
gt;A3: 52-1498BB3:59-0653A
gt;A4: 25-0038C
gt;
gt;
gt;How do I list all those numbers in Colum D,like:
gt;
gt;34-4015R
gt;59-2780B
gt;52-1498B
gt;25-0038C
gt;34-4017E
gt;59-2748B
gt;59-0653A
gt;34-4013E
gt;59-2785BOr if you prefer formulas...
Insert gt; Name gt; 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=quot;quot;,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.