19 Responses to “Merge three columns into one list in excel”

1. ExcelGuy says:

   January 26, 2011 at 6:52 pm

   Thanks A LOT!!! I really mean it. Lifesavers you guys are!

   Reply
2. Shawna says:

   March 21, 2011 at 7:22 pm

   Can you translate this into an earlier version of Excel please....I REALLY wish I had a clue how to use VBA (or how to write these formulas myself!)

   Reply
3. Oscar says:

   March 21, 2011 at 9:46 pm

   Shawna,

   Yes, the formula is large but it is not an array formula. I have included the excel 2003 solution in this blog post. Read above and you can also download an excel 2003 file.

   Reply
4. Shawna says:

   March 21, 2011 at 9:54 pm

   Thanks, now we're cooking!! Anyway to remove the blanks from the resulting "merged" list?

   Reply
5. Shawna says:

   March 21, 2011 at 10:02 pm

   nevermind...sorry I bothered you, I can't get the new formula to work at all, so it's pointless. Thanks for the time and effort anyway.

   Reply
6. Shawna says:

   March 21, 2011 at 10:26 pm

   Hmmm, tried again and I got it to work, but I still come up with zeros if one of my Lists has a blank. This is SO frustrating!!

   Reply
7. Oscar says:

   March 23, 2011 at 10:01 am

   Shawna,

   I hope this user defined function works in excel 2003:

   Function MergeRanges(ParamArray arguments() As Variant) As Variant()
   Dim cell As Range, temp() As Variant
   ReDim temp(0)
   For Each argument In arguments
     For Each cell In argument
       If cell <> "" Then
         temp(UBound(temp)) = cell
         ReDim Preserve temp(UBound(temp) + 1)
       End If
     Next cell
   Next argument
   ReDim Preserve temp(UBound(temp) - 1)
   MergeRanges = Application.Transpose(temp)
   End Function
   

   Where to copy the code?

   Press Alt-F11 to open visual basic editor
   Click Module on the Insert menu
   Copy and paste the user defined function into module
   Exit visual basic editor

   How to use user defined function in excel

   Select a cell
   Type MergeRanges(A1:C10, Sheet2!A3:B10) in formula bar, you can have as many range arguments you like.
   Press Ctrl + SHIFT + ENTER
   Add more cells to your selection.
   Click in Formula bar.
   Press Ctrl + SHIFT + ENTER

   Reply
8. shawna says:

   March 23, 2011 at 12:53 pm

   I wish I could try it, but I don't know how to create VBA. I'm a novice, though I did try a few times from random directions online, but it didn't work.

   Reply
9. shawna says:

   March 23, 2011 at 1:05 pm

   LOL, sorry...you gave me directions to follow, DUH! It's early, I'm not awake yet, so the attention hasn't kicked in. I'll let you know how I make out when I get to work. Thanks!

   Reply
10. Shawna says:

    March 23, 2011 at 3:00 pm

    Nope...I set up the following example:
    Heading1 Heading2 Heading3 Merge aa aa bb hh bb
    cc ii 1 cc
    2 dd
    dd jj 3 dd
    ee ee
    kk 4 ff
    ff ff
    gg gg
    #VALUE!
    #VALUE!
    #VALUE!

    I pasted the code in the VBA module and typed the statement:
    =MergeRanges(A2:A11, B2:B11, C2:C11) into the Merge cell. Then copied it down. However, notice only the A range merged. Also, the merged column repeated dd and ff (the only ones preceeded by a space). Any idea what I messed up?

    Reply
11. Oscar says:

    March 23, 2011 at 5:11 pm

    Shawna,

    Download excel file
    udf-merge-ranges.xls

    Udf is in cell range B3:B24, sheet1.

    Reply
12. Shawna says:

    April 13, 2011 at 9:57 pm

    Can we please discuss via email so I can attach an example of what I am doing? My typed example above was obviously worthless! LOL

    Thanks!

    Reply
13. Oscar says:

    April 14, 2011 at 6:17 am

    Shawna,
    Yes, you can use the contact form on this page: Contact me

    Reply
14. Peter says:

    May 20, 2011 at 4:06 am

    Hello,
    I downloaded your excel workbook and wanted to know how to combine lists that are 2000 items in length?

    Reply
15. BatTodor says:

    May 8, 2012 at 2:48 pm

    Hi Oscar,
    Here I found one interesting solution to merge table to single row. It could be useful for you
    http://www.cpearson.com/excel/TableToColumn.aspx
    I modified formula with INDEX() function (array formula) and like result
    =INDEX(Table;1+MOD(ROW()-ROW(MergedRange);ROWS(Table));1+TRUNC((ROW()-ROW(MergedRange))/ROWS(Table);0))

    Best regards

    Reply
16. BatTodor says:

    May 8, 2012 at 2:49 pm

    The solution is to merge table to single column, not row...

    Reply
17. Oscar says:

    May 11, 2012 at 3:25 pm

    BatTodor,

    Thanks for sharing!

    Reply
18. Zuberr says:

    February 8, 2013 at 6:30 pm

    Brilliant work, I converted List1, List2,List3 into dynamic range.

    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)

    Here link to solution with screenshot.
    http://stackoverflow.com/questions/14774806/how-to-combine-4-column-into-1-column

    Reply
19. Oscar says:

    February 11, 2013 at 10:34 am

    Zuberr,

    thank you!

    Reply