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.
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.
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
>
>
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:
I dont care if Col D hast blank cells too,I know how to remove them
34-4015R
59-2780B
52-1498B
25-0038C
34-4017E
59-2748B
59-0653A
34-4013E
59-2785B
Last edited by Wingman; 05-09-2006 at 03:08 PM.
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
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
<[email protected]> 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
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks