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

1. ExcelGuy Says:
   January 26th, 2011 at 6:52 pm
   
   

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

2. Shawna Says:
   March 21st, 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!)

3. Oscar Says:
   March 21st, 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.

4. Shawna Says:
   March 21st, 2011 at 9:54 pm
   
   

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

5. Shawna Says:
   March 21st, 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.

6. Shawna Says:
   March 21st, 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!!

7. Oscar Says:
   March 23rd, 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

8. shawna Says:
   March 23rd, 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.

9. shawna Says:
   March 23rd, 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!

10. Shawna Says:
    March 23rd, 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?

11. Oscar Says:
    March 23rd, 2011 at 5:11 pm
    
    

    Shawna,

    Download excel file
    udf-merge-ranges.xls

    Udf is in cell range B3:B24, sheet1.

12. Shawna Says:
    April 13th, 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!

13. Oscar Says:
    April 14th, 2011 at 6:17 am
    
    

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

14. Peter Says:
    May 20th, 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?

Leave a Reply