How to Combine 4 column into 1 column?

Question

Using office 2010.
everything is in same sheet.
Data in Column A B C & D can change (increase or decrease daily)

I have 4 column

                            OUTPUT --> IN column F should be 
---A-----B-----C------D---------------------------------------F
   1     5     8     AP                                       1
   2     6     9     BP                                       2
   3     7     1     CD                                       3
   4           5     QW                                       4
                                                              5
                                                              6
                                                              7
                                                              8
                                                              9
                                                              1
                                                              5
                                                              AP
                                                              BP
                                                              CD
                                                              QW

length of columns A B C & D can increase of decrease.

Hi pal) assuming "Data in Column A B C & D can change" - I'd look towards VBA. Have you tried anything? — Peter L., Feb 08 '13 at 14:34
I have got it to where it can combine in 1 column but won't keep up with range. — Mowgli, Feb 08 '13 at 14:37
Perfect Duplicate of http://stackoverflow.com/questions/2977660/excel-combine-multiple-columns-into-one-column — Pandian, Feb 08 '13 at 14:43
I have seen that too but that doesn't work with variation in rows — Mowgli, Feb 08 '13 at 14:45
Try this : http://www.get-digital-help.com/2009/06/28/merge-three-columns-into-one-list-in-excel/ — Pandian, Feb 08 '13 at 14:54

score 1 · Answer 1 · answered Feb 08 '13 at 14:39

1

How about this?

Sub move()
    Dim ws As Worksheet
    Dim outputColumn As Long
    Dim currentColumn As Long
    Dim currentOutputRow As Long

    Set ws = ActiveSheet
    outputColumn = 6 ' column f

    For currentColumn = 1 To 4
        currentOutputRow = ws.Cells(ws.Rows.Count, outputColumn).End(xlUp).Row
        If (currentOutputRow > 1) Then
            currentOutputRow = currentOutputRow + 1
        End If

        ws.Range(ws.Cells(1, currentColumn), ws.Cells(ws.Rows.Count, currentColumn).End(xlUp)).Copy _
            ws.Cells(currentOutputRow, outputColumn)
    Next
End Sub

answered Feb 08 '13 at 14:39

Joseph

5,070
1
25
26

Thanks it work but still few things, it includes row one also, it shouldn't and I know this will totaly mess things up what if it is not A B C D ? if it is I, O, P Q ? – Mowgli Feb 08 '13 at 14:50
@Mowgli You can modify it to use rows 2 and on by changing the last line where it copies from `ws.Cells(1, currentColumn)` to `ws.Cells(2, currentColumn)`. For the column issue, you would have to create an array with the columns you need and then loop through that instead of `1 To 4` – Joseph Feb 08 '13 at 18:27

score 1 · Answer 2 · answered Feb 08 '13 at 15:35

use the below. It accepts the range you need to change and will return a vertical array of values. To fill the values use an array formula.

Function ToVector(rng As Range)

    Dim cells()
    ReDim cells(rng.cells.Count)

    Dim i As Double

    For Each cell In rng

        cells(i) = cell
        i = i + 1

    Next cell

    ToVector = Application.WorksheetFunction.Transpose(cells)

End Function

score 1 · Answer 3 · answered Feb 08 '13 at 17:26

With the help of this site get-digital-help.com/

Combine Columns But this is only static.

I converted it to dynamic meaning changing range.

for example I posted A B C D IN F

To make formula more clear will enter formula in Name Manager

BELOW IS DYNAMIC FORMULA FOR EACH COLUMN (goes in name manger)

ALIST = =OFFSET($A$1,0,0,COUNTA($A:$A),1)
BLIST = =OFFSET($B$1,0,0,COUNTA($B:$B),1)
CLIST = =OFFSET($C$1,0,0,COUNTA($C:$C),1)
DLIST = =OFFSET($D$1,0,0,COUNTA($D:$D),1)

FORMULA IN COLUMN F and drag down

  =IFERROR(INDEX(ALIST, ROWS(F$1:$F1)), 
   IFERROR(INDEX(BLIST, ROWS(F$1:$F1)-ROWS(ALIST)), 
   IFERROR(INDEX(CLIST, ROWS(F$1:$F1)-ROWS(ALIST)-ROWS(BLIST)),
   IFERROR(INDEX(DLIST, ROWS(F$1:$F1)-ROWS(ALIST)-ROWS(BLIST)-ROWS(CLIST)),""))))

Screenshot

enter image description here

How to Combine 4 column into 1 column?

3 Answers3