Excel - Combine multiple columns into one column

Question

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

User · Answer

Not sure if this completely helps  but I had an issue where I needed a  smart  merge  I had two columns  A  amp  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    gt  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

User · Answer

You can combine the columns without using macros  Type the following function in the formula bar    IF ROW   lt  COUNTA A A  INDEX A A ROW    IF ROW   lt  COUNTA A B  INDEX B B ROW  -COUNTA A A   IF ROW   gt 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

User · Answer

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  amp  IIf Concat  lt  gt      myDelimiter       amp  r Text      End If    Next  End Function

User · Answer

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

User · Answer

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

[merge] Excel - Combine multiple columns into one column

Examples related to merge

Examples related to excel

Examples related to vba