Mauritius Finance

Nov 30 2017

Merge three columns into one list in excel #mail #merge #from #excel


#

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

Excel 2007 array formula in D2:

=IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, ROWS(D1:$D$1)-ROWS(List1)), IFERROR(INDEX(List3, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)), “”))) + CTRL + SHIFT + ENTER

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:

=INDEX((List1, List2, List3), ROW(A1)*(ROW(A1) =ROWS(List1))+(ROW(A1)-ROWS(List1))*(ROW(A1) ROWS(List1))*(ROW(A1) =(ROWS(List1)+ROWS(List2)))+(ROW(A1)-ROWS(List1)-ROWS(List2))*(ROW(A1) ROWS(List1))*(ROW(A1) (ROWS(List1)+ROWS(List2)))*(ROW(A1) =(ROWS(List1)+ROWS(List2)+ROWS(List3))). (ROW(A1) =ROWS(List1))*1+(ROW(A1) ROWS(List1))*(ROW(A1) =(ROWS(List1)+ROWS(List2)))*2+(ROW(A1) ROWS(List1))*(ROW(A1) (ROWS(List1)+ROWS(List2)))*(ROW(A1) =(ROWS(List1)+ROWS(List2)+ROWS(List3)))*3) + ENTER

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

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

Related articles

Sign up for free blog updates

  • Download free PDF Excel guide
  • Weekly new posts
  • Excel tips and tricks
  • Step by step tutorials
  • VBA macros & custom functions
  • Charts & Graphs

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

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?

R Vijayakumar says:

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.

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

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

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.

You can contact me through this webpage


Written by admin


Leave a Reply

Your email address will not be published. Required fields are marked *