Merge two columns
The picture above shows how to merge two columns into one list using a formula.
Table of Contents
1. Merge two columns vertically - Excel 365
The new VSTACK function, available for Excel 365 subscribers, handles this task easily. In fact, it is built solely to combine cell ranges or arrays.
This example shows how to merge two nonadjacent cell ranges with different sizes located in B3:B7 and D3:D5, the result is returned to cell F3 and cells below as far as needed.
This is a new behavior to dynamic array formulas in Excel 365, called spilling meaning values from a dynamic array formula are all returned if adjacent cells are empty.
Excel 365 dynamic array formula in cell F3:
Explaining formula
Step 1 - Populate arguments
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],...)
VSTACK(array1,[array2],...)
becomes
VSTACK(B3:B7, D3:D4)
Step 2 - Evaluate VSTACK function
VSTACK(B3:B7, D3:D4)
becomes
VSTACK({"AA";"DD";"CC";"GG";"HH"}, {"MM";"WW"})
and returns
{"AA"; "DD"; "CC"; "GG"; "HH"; "MM"; "WW"}.
2. Merge two columns vertically - earlier Excel versions
This example demonstrates a formula that only works in Excel 2007 and later versions, it utilizes the IFERROR function to move between cell ranges. However, the IFERROR function handles all formula errors and this may make it hard for you to spot other formula errors.
If you are looking for a formula to merge columns based on a condition read this article: Merge tables based on a condition
Formula in F3:
Copy cell C2 and paste it down as far as needed.
Earlier versions of excel, formula in C2:
Copy cell C2 and paste it down as far as needed.
How the formula in F3 works
Step 1 - Extracting List 1
=IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)-ROWS($A$2:$A$6)), ""))
The ROWS function calculate the number of rows in a cell range.
Function syntax: ROWS(array)
In cell C2: INDEX($A$2:$A$6, ROWS(C1:$C$1))
becomes
INDEX($A$2:$A$6, 1)
The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.
Function syntax: INDEX(array, [row_num], [column_num])
INDEX($A$2:$A$6, 1)
equals "AA"
In cell C3: INDEX($A$2:$A$6, ROWS(C1:$C$1))
becomes
INDEX($A$2:$A$6, ROWS(C2:$C$1))
becomes
INDEX($A$2:$A$6, 2) equals "DD"
and so on...
Step 2 - Error when all values in List 1 are processed
IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)-ROWS($A$2:$A$6)), ""))
In cell C7 something unexpected happens:
In cell C7: INDEX($A$2:$A$6, ROWS(C1:$C$1))
becomes
INDEX($A$2:$A$6, ROWS(C6:$C$1))
becomes
INDEX($A$2:$A$6, 6) equals "#REF!" error
There are no more values in List 1 so we need to continue on List 2.
IFERROR() takes care of this:
The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.
Function syntax: IFERROR(value, value_if_error)
Step 3 - Continue with List 2
In cell C7:
=IFERROR(INDEX($A$2:$A$6, ROWS(C6:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6)), ""))
becomes
=IFERROR(#REF!, IFERROR(INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6)), ""))
and becomes
IFERROR(INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6)), "")
INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6))
becomes
INDEX($B$2:$B$3, 6-ROWS($A$2:$A$6))
becomes
INDEX($B$2:$B$3, 6-5)
becomes
INDEX($B$2:$B$3, 1)
equals "MM" in List 2.
and so on...
Step 4 - Error when all values in List 2 and List 1 are evaluated
In cell C9:
=IFERROR(INDEX($A$2:$A$6, ROWS(C8:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C8:$C$1)-ROWS($A$2:$A$6)), ""))
becomes
=IFERROR(REF!, IFERROR(INDEX($B$2:$B$3, ROWS(C8:$C$1)-ROWS($A$2:$A$6)), ""))
becomes
=IFERROR(REF!, IFERROR(INDEX($B$2:$B$3, 8-ROWS($A$2:$A$6)), ""))
becomes
=IFERROR(REF!, IFERROR(INDEX($B$2:$B$3, 8-5), ""))
becomes
=IFERROR(REF!, IFERROR(REF!, ""))
equals "" (nothing)
3. Get Excel *.xlsx file
Combine merge category
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]
Excel categories
54 Responses to “Merge two columns”
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.
This article is terrific. Thanks so much for posting this solution!
I do have one question:
Let's say my "List 1" is auto updated and the number of entries in this list will fluctuate. Since the number of entries fluctuates, I would like to select a larger range than I actually have data in currently. The issue is when I make my "List 1" larger than the number of entries, the rows that don't currently have data in them, show up on my combined list as zeros.
So my question is, is there a way to adjust the formula so that when it looks at "List 1" for example, it skips over blank cells and continues to combine the list with "List 2".
Dan,
See this post: https://www.get-digital-help.com/2010/05/18/merge-two-columns-with-possible-blank-cells-in-excel-formula/
Have I missed something? I found the formula listed above worked just as well *without* entering it as an array formula.
Excel solution either way.
typo... *excellent solution either way
Thanks!!
Hi,I have little experience with excel.Could someone point out how to run this array formular? I entered the formular into the cell but it didn't give me the list.
Btw,I am using a mac.And the formular doesn't seem to work with openoffice on my linux anyway.
Jane,
It looks like IFERROR function and ISERROR function don´t exist in OpenOffice.
Oscar,
Thanks!If I want to merge multiple,say 5,columns in the same manner,how should I change the parameters then?Thanks and I am using excel now.
Try this udf:Combine cell ranges into a single range while eliminating blanks
is there a way to merge two columns of dates in this similar way but only combine the dates that appear on both columns? ie. without duplicating them in the newly created column?
macutan,
Array formula in cell D3:
Excel file
merge-two-columns-return unique distinct values.xlsx
(Excel 2007 Workbook *.xlsx)
Thank you Oscar for your reply,... I am using Excel 2003, which is probably why I am getting #NAME? in the cells where the merged list is supposed to appear... Any ideas as to how can i get this working in my excel version?
Also please note that as per list1 and list2 shown above, i would be looking for the merged list to have ONLY duplicate dates in it (as in dates that appear in both list1 and list 2) so in this case the merged list would display (also if possible in descending or ascending order) 2011-01-01 as that is the date that is in both lists.
Any guidance will be greatly appreaciated
Rgds
macutan
macutan
Array formula:
Read this post: How to find common values from two lists
Thank you Oscar, I have just tried to run your formula in an array but i am getting 2011-01-01 repeated until the end of the array instead of what you are seeing on the Merged list.
also, do you know how to modify the final array formula so that the dates come out descending.
macutan
Filter common dates from two columns and return unique distinct dates sorted smallest to largest
Array formula in cell D3:
How to create an array formula
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
Get the Excel file
sort-common-dates-from-two-columns.xls
Excel 97-2003 *.xls
Thanks a lot Oscar!!
I've never used the INDEX function before and this is a great use for it. My last puzzle is to automate the length of the merge column. Been trying to do with with a dynamic table height but no luck. anyone got ideas?
Cheers
Windfery,
I am not sure I understand, automate the length of the merge column?
You want the named ranges to expand whenever new values are added, right?
Windfery,
I updated this blog post.
Hey Oscar!!
Many thanks for sharing your excel knowledge...Ive been trying to figure this out for a while!...
I copied the formula into my worksheet (Im using excel 2003), but (and I cant see why), but the formula has only copied the first named range column (GN_LIST in column H) into the merged column (column M).
If I change the very last range name to the second list name (SH_LIST in column L), then the second column only is entered into the merged column.
My formula is as follows:
=IF(ISERROR(INDEX(GN_LIST, ROWS($M$1:M2))), IF(ISERROR(INDEX(SH_LIST, ROWS($M$1:M2)-ROWS(GN_LIST))), "", INDEX(SH_LIST, ROWS($M$1:M2)-ROWS(GN_LIST))), INDEX(GN_LIST, ROWS($M$1:M2)))
Can you PLEASE help?!? I am baffled...
Regards
I have resolved this now! Originally I wanted to define two separate columns as the two ranges as the data being impoted into them varies month by month i.e the data is dynamic. Through experimentation and testing, I discovered that your formula only works for statically defined ranges. It would be great to be able to adapt your formula to be used with dynamic ranges, but that is, sadly, beyond my excel scope and knowledge. If you are able to provide this, I would be deeply grateful, but if you are not, then I will use my work-around. Many thanks.
Dan,
Get the Excel 2003 *.xls file
merge-two-columns-dynamic-ranges1.xls
Received with many thanks!!!
I noticed that you have limited the height definition for the range to 10,000 cells. Is there any particular reason for this (ie. slower processing when more cells are included in the range), or can the height theoretically be increased down to cell 65,536?
Dan,
There is no partcular reason, it can be increased down to cell 65,536.
Thanks for commenting!
here is a version i used that auto counts the columns so you don't have to have your "list1" range = the range. I used this to combine multiple columns. Please mind that i didn't name my first indexranges which wouuld have simplified alot
=IFERROR(INDEX($AL$2:$AL$26, ROWS(BW$1:$BW15)), IFERROR(INDEX($AU$2:$AU$26, ROWS(BW$1:$BW15)-COUNT($AL$2:$AL$26)), IFERROR(INDEX($BA$2:$BA$26, ROWS(BW$1:$BW15)-SUM(COUNT($AL$2:$AL$26), COUNT($AU$2:$AU$26))), IFERROR(INDEX($BH$2:$BH$26, ROWS(BW$1:$BW15)-SUM(COUNT($AL$2:$AL$26), COUNT($AU$2:$AU$26), COUNT($BA$2:$BA$26))), IFERROR(INDEX($BO$2:$BO$26, ROWS(BW$1:$BW15)-SUM(COUNT($AL$2:$AL$26), COUNT($AU$2:$AU$26), COUNT($BA$2:$BA$26), COUNT($BH$2:$BH$26))), "")))))
Hey Oscar,
i am trying to merge 4 columns into one list but i think i have run into an issue. Can you help? I have already substitued my named ranges. One thing i have to note is that these named ranges are created using the following formula =OFFSET(References!$N$1,1,0,COUNTA(References!$N:$N)-1,1) so that i can use my named ranges in a dropdown box with data validation. These named ranges are always being updated so i dont want to have to continually change the range of the named reference. Let me know if you can help.
thanks JOEY
=IFERROR(INDEX(Phatec_Local_DIDs, ROWS(AM2:$AM$2)), IFERROR(INDEX(Phatec_Toll_Frees, ROWS(AM2:$AM$2)-ROWS(Phatec_Local_DIDs)), IFERROR(INDEX(Comcast_Local, ROWS(AM2:$AM$2)-ROWS(Phatec_Local_DIDs)-ROWS(Phatec_Toll_Frees)), IFERROR(INDEX(Comcast_Toll_Free, ROWS(AM2:$AM$2)-ROWS(Phatec_Local_DIDs)-ROWS(Phatec_Toll_Frees)-ROWS(ComCast_Local)),"")))) + CTRL + SHIFT + ENTER
one other thing that maybe you can help me with? i was able to use your two column method for combining two named ranges. Works great. The only issue now is when i go to sort the list it only sorts list one then sorts list two in the same column. Not sure if i explained that right but below is what i am experiencing.
List one:
216
220
221
223
305
311
314
315
360
361
501
505
511
List two
200
202
244
250
320
350
399
400
400
401
402
403
449
499
514
550
557
590
598
599
600
601
607
666
After using the sort feature it shows like this? each of the cells are in a table. Its like it sorts list one first then after sorting list one it sorts list two?
216
220
221
223
305
311
314
315
360
361
501
505
511
200
202
244
250
320
350
399
400
400
401
402
403
449
499
514
550
557
590
598
599
600
601
607
666
Joey,
Question 1:
You can see the logic behind this formula:
Merge three columns into one list in excel
and then adapt it to your formula.
Joey,
Question 2:
I recommend converting the formulas to values before sorting.
1. Select range
2. Copy (Ctrl + c)
3. Press with right mouse button on the same range.
4. Press with left mouse button on "Paste Special.."
5. Press with left mouse button on "Values"
6. Press with left mouse button on OK
This article is great.
Exactly what I needed.
Thanks so much for posting.
;-)
Thanks for commenting!
Great article. I have to combine 200 columns into one list. I know. I tried steps from 'Combine cell ranges into a single range while eliminating blanks' UDF, but looks like typing the formula itself is going to be a big deal. Any advice? (To give a bit of a background, I am trying to compare 200 columns to one column of data and figured it would be easier if I combine all 200 into one column and then compare, it would be easy).
Jinesh,
You want to know how to simplify/automate typing 200 column ranges in a udf?
Good question! I don´t know but I believe a macro should be able to return addresses of cell ranges populated with values.
Read this post:
Extract cell references from all cell ranges populated with values in a sheet
[...] [...]
I’m building an S-Curve Chart from disaster Condition Excel Spread sheet, that should reflect in the end: Plan and forecast weekly bars info and plan and forecast cumulative progress curves.
Take Plan Date Column & Using Pivot Table Fields: Row Labels and Values (count) I get an output such as:
Columns: a – Plan Date, b – linked value
Repeat above for Forecast Date using same Pivot Table Fields:
Columns: c – Forecast Date, d – linked value
Now I have 4 Columns: (a) Date + (b) linked value / (c) Date + (d) linked value.
Challenge, as I see it:
Compare dates columns a and c, c and a, find unique dates, somehow combine them into one column, but somehow keep linked value associates with each date:
So in the end I would get:
Column A – Date (for both Plan and Forecast)
Column B – Linked to Plan Date Value (if date is related to forecast value, then put 0 in plan column B for this row)
Column C – Linked to Forecast Date Value (if date is related to Plan value, then put 0 in forecast column C for this row)
So Ultimate goal is to have this database ready for a chart.
I’m sure there are various ways of manually doing it, but I also predict there must be a way of doing it all through Macro.
I have zero marco experience, so came to this forum with desperate need for a help. Please.
Oscar
thank you so much, it is just wonderful tricks you show here, now i can get rid of that macro in my excel dashboard.
thank you again and happy new year
[...] all, Please first have a look at this link so that you may follow me: Merge two columns into one list in excel | Get Digital Help - Microsoft Excel resource I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if [...]
Hi Oscar
Hope you are well!...
I have not asked for your assistance since January of last year because, after your invaluable help, I was able to complete the task I was working on.
I am now re-visiting the work as amendments need to be made and I find myself stuck again, as I have not Excel so indepth since.
Maybe you can help...
I asked you about merging to columns of dynamic data with possible blanks into one sorted column and you were able to help me do this.
An example of one such equation is provided below (I'm using 2003):
=IF(ISERROR(INDEX(ListGN, ROWS($O$1:O2))), IF(ISERROR(INDEX(ListSH, ROWS($O$1:O2)-ROWS(ListGN))), "", INDEX(ListSH, ROWS($O$1:O2)-ROWS(ListGN))), INDEX(ListGN, ROWS($O$1:O2)))
I now need to add a third column to this equation, and although I have a rough idea of what to do, I was hoping you could help me out as I'm rusty.
Many thanks!!
Dan,
Merge three columns into one list
Hi Oscar!
Many thanks for getting back to me. I had already seen this page but its not giving me what I want in the way as the previous expression posted above.
I have definied the third dynamic range, ListARGN
=OFFSET(core_calculations!$N$2,0,0,COUNTA(core_calculations!$N$2:$N$65536),1)
but am finding it quite difficult to logically workout how to expand the existing equation to take into account the new column of numbers.
I suppose an alternatiove would be to add another column to then merge and sort the first merged/sorted column with the new column. It should produce the same result but is not as tidy...
Can you assist at all? It would be a massive help!
Regards
Hi Oscar again!
I have just worked out what I was doing before and must admit that I got myself into a pickle! (I said I was rusty - haha)!
So, I have taken another look at your merge three columns into one list example and got things working correctly!
So, I must thank you again for this amazing website and your wonderful assistance!
Kindest regardss
Dan, can you explain your logic for the additional column? I have a total of 5 columns I need to convert into one list. Each column increases by time so they have to be individual columns. I am able to get the 3 columns to merge into one. Thank you very much!!!!!!!!!!!!!!!!!
Hi Oscar,
This is a great thread! I am having some trouble. I want to combine 2 lists into one list. I want only one record of duplicates and I want the unique items from both lists to be returned as well. The formula above is only returning me the first value from List 1 all the way down the column.
Thanks!
Jamie
Jamie,
The formula above is only returning me the first value from List 1 all the way down the column.
Make sure you entered the formula as an array formula. Also check your cell references, they might be wrong.
Using formula above to combine 2 data tables on separate sheets into a new sheet in one work book. I made sure the formula is correct and entered as array.
{=IFERROR(INDEX(Table_Query_from_QuickBooks_Data[[#Headers],[Name]], ROWS(A1:$A$1)), IFERROR(INDEX(Table_Query_from_QuickBooks_Data9[Name], ROWS(A1:$A$1)-ROWS(Table_Query_from_QuickBooks_Data[[#Headers],[Name]])), ""))}
It is only returning the data from table 1.
Does it have to be one table?
Oscar, your formula for merging two columns into one, works great! However, I am dealing with dates. I want to sort range to oldest to newest. I tried re-formatting and it seems to not work. Your comments or suggestions would be greatly appreciated. Thank you in advance -Carl
Carl Carter,
I don't know how to do that with a formula or array formula. It is possible with a User Defined Function.
Carl Carter
The UDF found on this webpage lets you merge and sort values from multiple cell ranges:
https://www.get-digital-help.com/sort-text-cells-alphabetically-from-two-columns-using-excel-array-formula/#sortudf
Thanks for your article, it inspired me to come up with the following two formulas. The first combines two columns of numbers into one without having to first define the length of both Array ranges or use the IFERROR. Assuming that the two list of numbers are in columns A and B,
=IF(ROWS($1:1)>COUNT($A:$A,$B:$B),"",IF(ISNUMBER($A2),$A2,INDEX($B:$B,ROW()-COUNT($A:$A))))
The crux of the formula is "IF(ISNUMBER($A2),$A2,INDEX($B:$B,ROW()-COUNT($A:$A))))". The beginning "IF(ROWS($1:1)>COUNT($A:$A,$B:$B)" returns a blank cell if the row number is greater than the combined count of both arrays, so that the formula can be copied down as far as one likes.
A simpler solution, if one wants to have the combined list sorted is the following:
=IF(ROWS($1:1)>COUNT($A:$A,$B:$B),"",SMALL(($A:$A,$B:$B),ROWS($1:1)))
Using nested brackets within the SMALL function, more than one array can be combined together. The added advantage of this formula is that it can handle blank cells or even cells with text within the desired list of numbers. Thanks again, Sam
Sam,
Thank you for your comment.
SAM the ****** genius
Problem if instead of blank cell will be formula or =""
what solution for this ?