## Merge three columns into one list in excel

**Question: **How do I merge three columns into one list?

**Answer:**

Excel 2007 array formula in D2:

This is an array formula, here is how to enter it. Type the formula in cell D2, press and hold CTRL + SHIFT simultaneously. Press Enter once. Release all keys. If you did it correctly, you now have curly brackets before and after the formula.

Copy cell D2 and paste it to D3:D12.

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

Excel 2003 and earlier versions in cell D2:

**Named ranges**

List1 (A2:A6)

List2 (B2:B3)

List3 (C2:C5)

**How to implement array formula to your workbook**

Change named ranges. If your merged list starts at, for example, F3. Change D1:$D$1 in the above formula to F2:$F$2.

### Download excel file

merge-three-columns.xlsx

(Excel 2007 Workbook *.xlsx)

### Download excel file

merge-three-columns_excel_2003.xls

(Excel 2007 Workbook *.xls)

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

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

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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 download 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:

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

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,

Download 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 downloaded 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 :)

http://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.

http://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:

http://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:

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