## Merge two columns with possible blank cells

This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is easy to modify if more columns are needed in the formula.

**What's on this page**

## Merge two columns with possible blank cells - Excel 365

This example works only in Excel 365, it contains three functions only available for Excel 365: LET, VSTACK and FILTER. A dynamic array formula is entered as a regular formula, however, it spills values to adjacent cells automatically as far as needed.

Dynamic array formula in cell F3:

### Explaining formula

#### Step 1 - Stack cell ranges vertically

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

VSTACK(*array1*, [*array2*], ...)

VSTACK(B3:B8,D3:D6)

becomes

VSTACK({"AA"; "DD"; 0; "GG"; "HH"; "TT"}, {"MM"; 0; "WW"; "TT"})

and returns

{"AA"; "DD"; 0; "GG"; "HH"; "TT"; "MM"; 0; "WW"; "TT"}.

#### Step 2 - Remove blanks

The FILTER function filter values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER(VSTACK(B3:B8,D3:D6),VSTACK(B3:B8,D3:D6)<>"")

becomes

FILTER(VSTACK(B3:B8,D3:D6),{"AA"; "DD"; 0; "GG"; "HH"; "TT"; "MM"; 0; "WW"; "TT"}<>"")

becomes

FILTER(VSTACK(B3:B8,D3:D6), {TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE})

becomes

FILTER({"AA"; "DD"; 0; "GG"; "HH"; "TT"; "MM"; 0; "WW"; "TT"}, {TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE})

and returns

{"AA"; "DD"; "GG"; "HH"; "TT"; "MM"; "WW"; "TT"}.

#### Step 3 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(*name1*, *name_value1*, *calculation_or_name2*, [*name_value2*, *calculation_or_name3*...])

FILTER(VSTACK(B3:B8,D3:D6),VSTACK(B3:B8,D3:D6)<>"")

VSTACK(B3:B8,D3:D6) is repeated twice in the formula above. I will name this intermediate calculation x.

LET(x,VSTACK(B3:B8,D3:D6),FILTER(x,x<>""))

The result is a smaller formula.

## Merge two columns with possible blank cells - earlier versions

**Question: **

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

*(You can find the question here: *Merge two columns into one list in excel)

**Answer:**

The array formula below removes blank cells. Another method is to use dynamic named ranges.

Array formula in C2:

Recommended post:

Recommended articles

The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

This is an array formula, here is how to enter it. Type the formula in cell C2, 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 C2 and paste it to cells below, as far as needed.

*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 post: Merge lists with criteria*

### Named ranges

Recommended article:

Recommended articles

The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]

### Explaining array formula in cell C8

#### Step 1 - Understand relative and absolute cell references

In cell C2 the formula is:

=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))

In cell C8 the formula is:

=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A7))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))

Recommended articles

What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]

#### Step 1 - Find cells containing a value in List 1 and return row numbers in an array

=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A7))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))

IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1)

becomes

IF(ISBLANK({"AA";"DD";"";"GG";"HH";"TT"}), "", ROW(A2:A7)-MIN(ROW(A2:A7))+1)

becomes

IF({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}, "", {2;3;4;5;6;7}-MIN({2;3;4;5;6;7})+1)

becomes

IF({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}, "", {2;3;4;5;6;7}-2+1)

becomes

IF({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}, "", {1;2;3;4;5;6})

and returns

{1;2;"";4;5;6}

Recommended articles

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

#### Step 2 - Return the k-th smallest row number

=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A7))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))

SMALL(array,k) Returns the k-th smallest number in this data set.

SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A7))

becomes

SMALL({1;2;"";4;5;6}, 8 )

and returns #NUM

Recommended articles

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

#### Step 3 - Return a value of the cell at the intersection of a particular row and column

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

INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A7)))

becomes

INDEX(List1, #NUM)

and returns

#NUM

Recommended articles

Gets a value in a specific cell range based on a row and column number.

#### Step 4 - Check if formula returns an error

becomes

=IFERROR(#NUM, IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))

becomes

IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), "")

Recommended articles

The IFERROR function lets you catch most errors in Excel formulas. It was introduced in Excel 2007. In previous Excel […]

#### Step 5 - Find cells containing a value in List 2

IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), "")

IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1)

becomes

IF(ISBLANK({"MM";"";"WW";"TT"}), "", {2;3;4;5}-MIN({2;3;4;5})+1)

becomes

IF({FALSE;TRUE;FALSE;FALSE}), "", {1;2;3;4})

and returns

{1;"";3;4}

#### Step 6 - Return the k-th smallest row number

IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A8)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), "")

SMALL(array,k) Returns the k-th smallest number in this data set.

SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A8)-SUMPRODUCT(--NOT((ISBLANK(List1)))))

becomes

SMALL({1;"";3;4}, ROW(A7)-SUMPRODUCT(--NOT((ISBLANK(List1)))))

becomes

SMALL({1;"";3;4}, 7-SUMPRODUCT(--NOT((ISBLANK({"AA";"DD";"";"GG";"HH";"TT"})))))

becomes

SMALL({1;"";3;4}, 7-SUMPRODUCT(--NOT({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE})))

becomes

SMALL({1;"";3;4}, 7-SUMPRODUCT(--{TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}))

becomes

SMALL({1;"";3;4}, 7-SUMPRODUCT({1;1;0;1;1;1})

becomes

SMALL({1;"";3;4}, 7-5)

becomes

SMALL({1;"";3;4}, 2)

and returns 3.

#### Step 6 - Return a value of the cell at the intersection of a particular row and column

IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A8)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), "")

becomes

IFERROR(INDEX(List2, 3), "")

becomes

IFERROR(INDEX({"MM";0;"WW";"TT"}, 3), "")

becomes

IFERROR("WW", "")

and returns WW.

**Get Excel sample file for this tutorial. **

merge-two-columns with blanks.xlsx

(Excel 2007 Workbook *.xlsx)

### Combine merge category

The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]

Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]

The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]

Question: I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The […]

This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]

The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

This article explains how to merge values row by row based on a condition in column A using an array […]

Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 33 Responses to “Merge two columns with possible blank cells”

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

**Contact Oscar**

You can contact me through this contact form

=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))

please same for office 2003

Prash,

Excel 2003, array formula in C2:

=IF(ISERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1)))), IF(ISERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1))))))), "", INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1))))))), INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1)))) + CTRL + SHIFT + ENTER

Copy cell C2 and paste it down as far as needed.

Could you please tell me what I need to do to add additional Lists? I'm trying to get 3 lists merged into a single list with no blanks for earlier versions of Excel. Thanks

Shawna,

The array formula would be ridiculously large, too large I think.

I was hoping to be able to add additional lists as well with the ability to remove the spaces. It's not to bad if you leave the spaces between the end of one list and the start of the next in.

=IFERROR(INDEX(List1,ROWS(V$2:V2)),IFERROR(INDEX(List2,ROWS(V$2:V2)-ROWS(List1)),IFERROR(INDEX(List3,ROWS(V$2:V2)-ROWS(List1)-ROWS(Lis2)),"")))

Instead I think I will just do a simply =cell in a new worksheet and than simply sort it.

Denis,

Thanks for a reply...if my data are in columns, do I simply change the formula to read "columns"?

Here is my dilemma: I work in a forensic lab and we typically have samples that undergo different procedures, culminating in a final procedure they all undergo. I am trying to compile a Total Sample List (which combines all of the samples from other lists). The data from the 'Total Sample List' will be drawn into MS Word to populate the forms we use according to sample type (that much I've already figured out). I'm a lab geek, not a computer geek UNFORTUNATELY!

Well, honestly I would have to say just give it a try. You could look at it as an experiment :)

I'm by no means an expert in excel. I spent a couple hours just modifying the above formula from one that was posted on this website prior.

I've been trying to create something close to this for a long time now for budgeting and cashflow of my personal finances.

-Good Luck!

Can this work, with the same dataset specified above, if the lists are defined :

List1 (A2:A10)

List2 (B2:B10)

instead of

List1 (A2:A7)

List2 (B2:B5)

that is, with trailing blank cells in the list. In my particular example, my lists will be filled top down, with any blank cells at the end, and i do know the maximum number of cells i could possibly have, if that helps.

Cause when i try this, i only get details from the first list.

Thanks

Graeme

Hi All,

I hope you are able to help me, must admit I don't do a whole lot with excel in my day to day role so my skills are really limited. The above formula functions OK in my workbook and combines two named ranges together into a single list with no 0's :) so all good.

My question is how would I add a third list, fourth list etc. etc to the formula ?

I'm attempting to set up one list that combines data from several other lists.

Thanks very much

Nathan

I have all 13 lists combined now using the below formula, now just struggling to add in the "Remove Balnks" piece, My application only allows imports from xlsx and not xlsm so I cannot do it the easy way (with a vb macro)

also the application will not recognize the file if it's been renamed :(

Formula

=IFERROR(INDEX(List1,ROWS(V$2:V2)),

IFERROR(INDEX(List2,ROWS(V$2:V2)-ROWS(List1)),

IFERROR(INDEX(List3,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)),

IFERROR(INDEX(List4,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)),

IFERROR(INDEX(List5,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)),

IFERROR(INDEX(List6,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)),

IFERROR(INDEX(List7,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)),

IFERROR(INDEX(List8,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)),

IFERROR(INDEX(List9,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)),

IFERROR(INDEX(List10,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)-ROWS(List9)),

IFERROR(INDEX(List11,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)-ROWS(List9)-ROWS(List10)),

IFERROR(INDEX(List12,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)-ROWS(List9)-ROWS(List10)-ROWS

(List11)),

IFERROR(INDEX(List13,ROWS(V$2:V2)-ROWS(List1)-ROWS(List2)-ROWS(List3)-ROWS(List4)-ROWS(List5)-ROWS(List6)-ROWS(List7)-ROWS(List8)-ROWS(List9)-ROWS(List10)-ROWS

(List11)-ROWS(List12)),""))

Thanks

Nathan

I think I have this sorted now, I have used dynamic named ranges for List1 --> List13, in the refers to formula is:-

=OFFSET(Sheet1!$A$2,0,0,MATCH("*",Sheet1!$A:$A,-1),1)

this formula seems to work OK in as much that it does not include the column heading "Cell A1" but for some reason it is adding one row to the end?

so In my drop down I see:-

Value - sheet 1

Value - sheet 1

0

Value - sheet 2

0

Value - sheet 3

Value - sheet 3

0

looking at the named range I can see it's selecting one row more than there are values - can anyone explain why it's doing this ? I am sure it's a simple fix

Thanks

Nathan

Nathan,

try this named range:

=OFFSET(Sheet1!$A$2,0,0,MATCH("*",Sheet1!$A:$A,-1)-1,1)

Hi Oscar,

Thanks for getting back to me, I got this working using :-

=IFERROR(INDEX(Servers!A:A,AGGREGATE(15,6,(ROW(Servers!A:A)-ROW(Servers!A3)+1)/(Servers!A:A""),ROWS(C$1:C2))),"")

for each of the 13 lists and then used these ranges to make the drop down

Thanks

Nate

Hi Nathan,

I've been looking all over the internet for your solution to combining multiple columns that remove blanks into 1 cell. Can you please post an example file?

Thanks,

Andy

Dear Oscar,

I need to have both functions of columns combine (Skip banks + Distinct Values).

Please help.

Regards,

Alaa Abdullnabi,

See this file:

how-to-extract-a-unique-list-from-two-columns-in-excel-2007-no-blanks.xlsx

GREATTTTTT, REALLY MANY MANY THANKS... YOU ARE ONE OF THE BEST.

Hi Oscar,

I'm trying to combine Column "A" values from multiple sheets (within same workbook) in condition that column "C" contain specific word/value e.g. "Good" within the same sheets. It's like filtering multiple sheets in one where specific value/ word is there.

Your help is appreciated.

Thanks

Alaa

Alaa Abdullnabi,

Get the Excel *.xlsx file

merge-two-columns-with-blanks-with-condition.xlsx

An alternative , and probably simpler approach has been presented by Sajan Thomas in the Chandoo.org forum , here :

https://chandoo.org/forums/topic/how-to-transpose-multiple-rows-into-one-column-without-0-in-excel

NARAYAN,

Yes, it is simpler but the solution presented here can transpose two non-contiguous columns.

Thanks for commenting!

I have a feeder list that creates 2 list, I have implemented your forumula and it works well, however the blanks in my list are created as spaces because a formula is returning a blank, is there any way I can tailor your formula to accept this condition and eliminate it from the final list, at the moment it is putting the "blanks into the combined list".

Cheers

JD

Hi John,

I have aexactly the same problem, did you find a way around it i.e. to ignore blanks that contain formulas? If so, do you have the formula?

Thanks

Unsure why this formula isn't removing blanks..

=ArrayFormula(IFERROR(INDEX(A$3:A$20, SMALL(IF(ISBLANK(A$3:A$20), "", ROW(A$3:A$20)-MIN(ROW(A$3:A$20))+1), ROW(A1))), IFERROR(INDEX(C$3:C$20, SMALL(IF(ISBLANK(C$3:C$20), "", ROW(C$3:C$20)-MIN(ROW(C$3:C$20))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(A$3:A$20)))))), "")))

Anyone able to shed some light?

Hi, this formula worked for me. It is consolidating comments made on other worksheets. Now I would like to add the name of the person who made the comment. How can I do that? for example, The name is located on A2 on each corresponding sheet.

IFERROR(IFERROR(INDEX(Additives,MATCH(0,IF(ISBLANK(Additives),1,COUNTIF($B$1:B100,Additives)),0)),INDEX(AdminBuilding,MATCH(0,IF(ISBLANK(AdminBuilding),1,COUNTIF($B$1:B100,AdminBuilding)),0))),"")

Does the original formula have a version using IF(ISERROR), using it with OO Calc?

This is great!! Thanks for sharing :) Is there a way to account for "Blank" cells that contain a formula? The cells do not contain data, so I'd like to skip them, but since we use the ISBLANK function, they are not registering as Blank...?

Thank you!!!

Dana,

Yes, it is possible:

sir, How to Merge four columns with possible blank cells. please give me example. Its very urgent. My email id is [email protected].

How do I use this method on 6 columns/lists?

What would have to be added to the end of your formula to add 3rd dynamic range list into the merged column?

=IFERROR(INDEX(List1, SMALL(IF(ISBLANK(List1), "", ROW(List1)-MIN(ROW(List1))+1), ROW(A1))), IFERROR(INDEX(List2, SMALL(IF(ISBLANK(List2), "", ROW(List2)-MIN(ROW(List2))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(List1)))))), ""))

Nevermind, figured it out for myself.

=IFERROR(INDEX(B$1:B$22, SMALL(IF(ISBLANK(B$1:B$22), "", ROW(B$1:B$22)-MIN(ROW(B$1:B$22))+1), ROW(A1))), IFERROR(INDEX(C$1:C$22, SMALL(IF(ISBLANK(C$1:C$22), "", ROW(C$1:C$22)-MIN(ROW(C$1:C$22))+1), ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(B$1:B$22)))))), IFERROR(INDEX(D$1:D$22, SMALL(IF(ISBLANK(D$1:D$22), "", ROW(D$1:D$22)-MIN(ROW(D$1:D$22))+1),ROW(A1)-SUMPRODUCT(--NOT((ISBLANK(B$1:C$22)))))),"")))

Michael,

thank you for posting the solution. I have added an Excel 365 formula to this article, it is much easier to adjust the formula to include more cell ranges.