CODEWITHSUNDEEP
mergeexcelvba
Akib
Akib

Reputation: 111

Excel - Combine multiple columns into one column

I have multiple lists that are in separate columns in excel. What I need to do is combine these columns of data into one big column. I do not care if there are duplicate entries, however I want it to skip row 1 of each column.

Also what about if ROW1 has headers from January to December, and the length of the columns are different and needs to be combine into one big column? 

ROW1| 1   2   3    
ROW2| A   D   G    
ROW3| B   E   H    
ROW4| C   F   I

should combine into 

A    
B    
C    
D    
E    
F    
G    
H    
I

The first row of each column needs to be skipped.

Upvotes: 11

Views: 138876

Answers (6)

Charles Lehnen
Charles Lehnen

Reputation: 111

I was dealing with this issue and found a solution I think is very elegant as well as expandable to more columns:

=VSTACK(FILTER(A:A,A:A<>""),FILTER(B:B,B:B<>""))

Upvotes: 0

CristinaP
CristinaP

Reputation: 336

You can combine the columns without using macros. Type the following function in the formula bar:

=IF(ROW()<=COUNTA(A:A),INDEX(A:A,ROW()),IF(ROW()<=COUNTA(A:B),INDEX(B:B,ROW()-COUNTA(A:A)),IF(ROW()>COUNTA(A:C),"",INDEX(C:C,ROW()-COUNTA(A:B)))))

The statement uses 3 IF functions, because it needs to combine 3 columns:

  • For column A, the function compares the row number of a cell with the total number of cells in A column that are not empty. If the result is true, the function returns the value of the cell from column A that is at row(). If the result is false, the function moves on to the next IF statement.
  • For column B, the function compares the row number of a cell with the total number of cells in A:B range that are not empty. If the result is true, the function returns the value of the first cell that is not empty in column B. If false, the function moves on to the next IF statement.
  • For column C, the function compares the row number of a cell with the total number of cells in A:C range that are not empty. If the result is true, the function returns a blank cell and doesn't do any more calculation. If false, the function returns the value of the first cell that is not empty in column C.

Upvotes: 11

user1825083
user1825083

Reputation:

I created an example spreadsheet here of how to do this with simple Excel formulae, and without use of macros (you will need to make your own adjustments for getting rid of the first row, but this should be easy once you figure out how my example spreadsheet works):

https://docs.google.com/a/umich.edu/spreadsheet/ccc?key=0AuSyDFZlcRtHdGJOSnFwREotRzFfM28tWElpZ1FaR2c#gid=0

Upvotes: 3

Alex P
Alex P

Reputation: 12497

Try this. Click anywhere in your range of data and then use this macro:

Sub CombineColumns()
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer

Set rng = ActiveCell.CurrentRegion
lastCell = rng.Columns(1).Rows.Count + 1

For iCol = 2 To rng.Columns.Count
    Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
    ActiveSheet.Paste Destination:=Cells(lastCell, 1)
    lastCell = lastCell + rng.Columns(iCol).Rows.Count
Next iCol
End Sub

Upvotes: 21

Mark Baker
Mark Baker

Reputation: 212522

Function Concat(myRange As Range, Optional myDelimiter As String) As String 
  Dim r As Range 
  Application.Volatile 
  For Each r In myRange 
    If Len(r.Text) Then 
      Concat = Concat & IIf(Concat <> "", myDelimiter, "") & r.Text 
    End If 
  Next 
End Function

Upvotes: 1

Steven Dorfmeister
Steven Dorfmeister

Reputation: 200

Not sure if this completely helps, but I had an issue where I needed a "smart" merge. I had two columns, A & B. I wanted to move B over only if A was blank. See below. It is based on a selection Range, which you could use to offset the first row, perhaps.

Private Sub MergeProjectNameColumns()
    Dim rngRowCount As Integer
    Dim i As Integer

    'Loop through column C and simply copy the text over to B if it is not blank
    rngRowCount = Range(dataRange).Rows.Count
    ActiveCell.Offset(0, 0).Select
    ActiveCell.Offset(0, 2).Select
    For i = 1 To rngRowCount
        If (Len(RTrim(ActiveCell.Value)) > 0) Then
            Dim currentValue As String
            currentValue = ActiveCell.Value
            ActiveCell.Offset(0, -1) = currentValue
        End If
        ActiveCell.Offset(1, 0).Select
    Next i

    'Now delete the unused column
    Columns("C").Select

    selection.Delete Shift:=xlToLeft
End Sub

Upvotes: 1

Related Questions