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:

=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)))))), ""))

Recommended post:

Excel udf: Combine cell ranges into a single range while eliminating blanks

This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes […]

Comments(4) Filed in category: Combine/Merge, Excel

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

List1 (A2:A7)
List2 (B2:B5)

Recommended article:

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: =IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, […]

Comments(26) Filed in category: Combine/Merge, Excel

Explaining array formula in cell C8

Step 1 - Understand relative and absolute cell referencing

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)))))), ""))

How to use absolute and relative references in excel

What is a refererence in excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

Comments(12) Filed in category: Excel

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}

IF function explained

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

Comments(9) Filed in category: Excel

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

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel

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

=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)))))), ""))

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

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Step 4 - Check if formula returns an error

=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)))))), ""))

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)))))), "")

IFERROR function

The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR […]

Comments(0) Filed in category: 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.

Download excel sample file for this tutorial.
merge-two-columns with blanks.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

ISBLANK(value)
Checks whether a reference is to an empty cell and returns TRUE or FALSE

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW function explained

ROW(reference) Returns the row number of a reference. You can enter a reference to a single cell or a cell range. […]

Comments(1) Filed in category: Excel, Row

How to use Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

Comments(2) Filed in category: Excel, SUMPRODUCT function

NOT(logical)
Changes FALSE to TRUE, or TRUE to FALSE