Merge three columns into one list
The above image demonstrates a formula that adds values in three different columns into one column.
Table of Contents
1. Merge three columns into one list - Excel 365
Excel 365 subscribers can access new array manipulation formulas that make working with arrays and cell ranges much easier, one of those new functions is the VSTACK function.
It allows you to merge multiple cell ranges vertically, meaning the second cell range/array is joined below the first cell range/array. The result is a dynamic array formula that spills values below as far as needed.
This example shows how to merge three nonadjacent cell ranges with different sizes, cell range B3:B7 has five values. Cell range D3:D4 has two values, and F3:F6 has four values. The formula is entered in cell H3 and the array values are spilled to cell H3 and cells below as far as needed.
Excel 365 dynamic array formula in cell H3:
Explaining formula
The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)
Function syntax: VSTACK(array1,[array2],...)
2. Merge three columns into one list - earlier Excel versions
This example works in earlier Excel versions from 2007 to Excel 2019, it requires a more complicated formula because these versions don't have the VSTACK function.
The IFERROR function is used a lot in this example and I want to warn you that it also handles all formula errors, this may
Formula in H3:
Copy cell H2 and paste to the cells below.
Explaining formula in cell H2
The IFERROR function moves the calculation to the next part (formula2) when the first part (formula1) begins to return errors. That is also true for the second part (formula2), when errors occur the calculation continues with the third part (formula3)
IFERROR(IFERROR(formula1, formula2), formula3)
Formula1 extracts values from List1. Formula2 extracts values from List2. Formula3 extracts values from List3.
Step 1 - Count cells vertically
The ROWS function counts rows in a cell reference. H2:$H$2 is special, it expands as the formula is copied to the cells below.
ROWS(H2:$H$2)
returns 1.
Step 2 - Return value
The INDEX function returns values from a cell range based on a row number and column number.
INDEX($B$3:$B$7, ROWS(H2:$H$2))
becomes
INDEX($B$3:$B$7, 1)
becomes
INDEX({"AA";"DD";"CC";"GG";"HH"}, 1)
and returns "AA" in cell H3.
Step 3 - Loop
When the formula starts returning errors the second part of the formula begins.
INDEX($D$3:$D$4, ROWS(H2:$H$2)-ROWS($B$3:$B$7))
It also takes into account the number of values returned from the first cell range, for example in cell H8:
INDEX($D$3:$D$4, ROWS(H7:$H$2)-ROWS($B$3:$B$7))
becomes
INDEX($D$3:$D$4, 6-ROWS($B$3:$B$7))
becomes
INDEX($D$3:$D$4, 6-5)
becomes
INDEX({"MM";"WW"}, 1)
and returns "MM" in cell H8.
3. Merge three columns into one list - Excel 2003 and earlier versions
Formula in cell H3:
Named ranges
List1 (A2:A6)
List2 (B2:B3)
List3 (C2:C5)
4. Get Excel file
Combine merge category
The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]
Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]
Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]
This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]
The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]
This article explains how to merge values row by row based on a condition in column A using an array […]
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
29 Responses to “Merge three columns into one list”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Thanks A LOT!!! I really mean it. Lifesavers you guys are!
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!)
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 get an Excel 2003 file.
Thanks, now we're cooking!! Anyway to remove the blanks from the resulting "merged" list?
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.
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!!
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 FunctionWhere to copy the code?
Press Alt-F11 to open visual basic editor
Press with left mouse button on 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.
Press with left mouse button in Formula bar.
Press Ctrl + SHIFT + ENTER
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. :(
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!
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?
Shawna,
Get the Excel file
udf-merge-ranges.xls
Udf is in cell range B3:B24, sheet1.
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! :)
Shawna,
Yes, you can use the contact form on this page: Contact me
Hello,
I gotr your Excel workbook and wanted to know how to combine lists that are 2000 items in length?
Hi Oscar,
Here I found one interesting solution to merge table to single row. It could be useful for you :)
https://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
The solution is to merge table to single column, not row...
BatTodor,
Thanks for sharing!
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.
https://stackoverflow.com/questions/14774806/how-to-combine-4-column-into-1-column
Zuberr,
thank you!
This is a very useful formula. But, here the list names have fixed ranges and if the ranges expand or reduces, then the formula does not hold good. For example, list1 range A1:A20 with data upto A7, list2 range B1:B20 with data upto row b17 and list3 range C1:C20 with data upto C10. In simple words, the ranges need to be dynamic. Can anybody help with a formula (not Vba). Regards.
R Vijayakumar,
Try this:
https://www.get-digital-help.com/2011/05/17/excel-charts-use-dynamic-ranges-to-add-new-values-to-both-chart-and-drop-down-list/
i have tried in different ways and found this array formula works....
=IFERROR(IFERROR(IFERROR(INDEX(MultiPlyYarnPRCount, MATCH(0, COUNTIF(L$7:$L7, MultiPlyYarnPRCount), 0)), INDEX(MultiPlyYarnDHPRCount, MATCH(0, COUNTIF(L$7:$L7, MultiPlyYarnDHPRCount), 0))),INDEX(MultiPlyYarnDHCRCount, MATCH(0, COUNTIF(L$7:$L7, MultiPlyYarnDHCRCount),0))),"")
MultiPlyYarnPRCount (I8:I100),MultiPlyYarnDHPRCount (J8:J100) and MultiPlyYarnDHCRCount (K8:K100) are three ranges in three columns, each having varying lengths of data i.e. first range has data in only one row, second range 23 rows and third one has 18 rows of data. This formula works fine.
I request your suggestion for fine-tuning the formula if possible.
Regards,
I really need help!
Have similar but bigger issue
Have three columns in excel:
Column A: Category
Column B: Value
Column C: Value
I want to consolidate the list such that if I have a row like:
A1=Car | B1=Ford | C1=Toyota
A2=Scooter | B1=Honda | C1=(blank)
Gives me a result:
A1=Car | B1=Ford
A2=Car | B2=Toyota
A3=Scooter | A3=Honda
Sorry, correction:
I want to consolidate the list such that if I have a row like:
A1=Car | B1=Ford | C1=Toyota
A2=Scooter | B2=Honda | C2=(blank)
Gives me a result:
A1=Car | B1=Ford
A2=Car | B2=Toyota
A3=Scooter | B3=Honda
How can I merge 5 columns?
thanks
anthony,
The easiest way to go is to use a simple user defined function:
Function MergeRanges(ParamArray arguments() As Variant) As Variant() Dim cell As Range, All As String For Each argument In arguments For Each cell In argument All = All & cell & "," Next cell Next argument MergeRanges = Application.Transpose(Split(All, ",")) End FunctionWhere to copy the code?
Press Alt-F11 to open visual basic editor
Press with left mouse button on 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.
Press with left mouse button in Formula bar.
Press Ctrl + SHIFT + ENTER
Hi people,
Here's a solution i'm trying to solve using the functionality/formula provided at the top of this post .
Let's substitute List1, List 2 and List3 ranges with dynamic ranges that are using offsets when referring to data filter results elsewhere in my workbook. these dynamic ranges sometimes return no data as there are no results at times in my filters . my problem is, when one of the dynamic ranges has no data in it, that then corrupts the merge.
My mind is thinking incorporating ISBLANK or something similar to the above IFERROR INDEX ROWS formula where an empty dynamic range doesn't then corrupt the merge.
Hope that makes sense. Am drowning in development deadlines, any help would be much appreciated.
Hi Geoff
Have you read this article?
https://www.get-digital-help.com/2010/05/18/merge-two-columns-with-possible-blank-cells-in-excel-formula/
I've been looking at this article as well as the article on "Merge two columns with possible blank cells", but I'm trying to find out how to do a union of the two:
How do I merge multiple (3+) columns into one, where there are blank or #N/A cells involved?
I can use the Two-Into-One article just fine for handling blanks (or changing isblank to isna to handle #N/A values), but I haven't been able to get it to handle 3+ columns even after trying to combine the parsing of both articles together.