Merge two columns into one list in excel
Question: How do I merge two ranges into one list?
Answer:
Excel 2007 array formula in C2:
Earlier versions of excel, array formula in C2:
Named ranges
List1 (A2:A6)
List2 (B2:B3)
What is named ranges?
How to create dynamic named ranges
Excel 2007/2010 users: Convert the ranges to excel tables and update formula references accordingly.
Previous excel versions:
List1: =OFFSET(Sheet1!$B$3, 0, 0, COUNTA(Sheet1!$B:$B)-1, 1)
List2: =OFFSET(Sheet1!$C$3, 0, 0, COUNTA(Sheet1!$C:$C)-1, 1)
How to implement array formula to your workbook
Change named ranges. If your merged list starts at, for example, F3. Change C1:$C$1 in the above formula to F2:$F$2.
How Excel 2007 array formula in C2 works
Step 1 - Extracting List 1
=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), ""))
In cell C2: INDEX(List1, ROWS(C1:$C$1))
becomes
INDEX(List1, 1) equals "AA"
In cell C3: INDEX(List1, ROWS(C1:$C$1))
becomes
INDEX(List1, ROWS(C2:$C$1))
becomes
INDEX(List1, 2) equals "DD"
and so on...
Step 2 - Error when all values in List 1 are processed
=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), ""))
In cell C7 something unexpected happens:
In cell C7: INDEX(List1, ROWS(C1:$C$1))
becomes
INDEX(List1, ROWS(C6:$C$1))
becomes
INDEX(List1, 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:
IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise
Step 3 - Continue with List 2
In cell C7:
=IFERROR(INDEX(List1, ROWS(C6:$C$1)), IFERROR(INDEX(List2, ROWS(C6:$C$1)-ROWS(List1)), ""))
becomes
=IFERROR(#REF!, IFERROR(INDEX(List2, ROWS(C6:$C$1)-ROWS(List1)), ""))
and becomes
IFERROR(INDEX(List2, ROWS(C6:$C$1)-ROWS(List1)), "")
INDEX(List2, ROWS(C6:$C$1)-ROWS(List1))
becomes
INDEX(List2, 6-ROWS(List1))
becomes
INDEX(List2, 6-5)
becomes
INDEX(List2, 1) equals "MM" in List 2
and so on...
Step 4 - Error when all values in List 2 and List 1 are processed
In cell C9:
=IFERROR(INDEX(List1, ROWS(C8:$C$1)), IFERROR(INDEX(List2, ROWS(C8:$C$1)-ROWS(List1)), ""))
becomes
=IFERROR(REF!, IFERROR(INDEX(List2, ROWS(C8:$C$1)-ROWS(List1)), ""))
becomes
=IFERROR(REF!, IFERROR(INDEX(List2, 8-ROWS(List1)), ""))
becomes
=IFERROR(REF!, IFERROR(INDEX(List2, 8-5), ""))
becomes
=IFERROR(REF!, IFERROR(REF!, "")) equals "" (nothing)
Download excel sample file for this tutorial.
merge-two-columns2.xlsx
(Excel 2007 Workbook *.xlsx)
Functions in this article:
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
ROWS(array) returns the number of rows in a reference or an array
IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise
ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE
Related posts:
Merge three columns into one list in excel
Merge two columns with possible blank cells in excel (formula)
Extract a list of duplicates from two columns combined using array formula in excel
Recommended categories
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: http://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:
Download 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.
Download 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,
Download 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. Right click on the same range.
4. Click "Paste Special.."
5. Click "Values"
6. Click 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 [...]