## 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:**

This example merges two columns into one column using an array formula. If you are looking for merging two data lists with criteria, check this out: Merge lists with criteria

**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

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 filemerge-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 largestArray formula in cell D3:How to create an array formulaCopy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.

Press and hold Ctrl + Shift.

Press Enter once.

Release all keys.

Download excel filesort-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 [...]

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

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?