Merge two columns with possible blank cells in excel (formula)
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:
Copy cell C2 and paste it down as far as needed.
Named ranges
List1 (A2:A7)
List2 (B2:B5)
What is named ranges?
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)))))), ""))
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}
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
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
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)))))), "")
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:
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
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
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(reference) returns the rownumber of a reference
IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise
SMALL(array,k) Returns the k-th smallest number in this data set.
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
NOT(logical)
Changes FALSE to TRUE, or TRUE to FALSE
Related blog posts
- Merge two columns into one list in excel
- Merge three columns into one list in excel
- Identify missing values in two columns using excel formula
- Sort text cells alphabetically from two columns using excel array formula
- Filter duplicates from two columns combined and sort from A to Z using array formula in excel
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
December 23rd, 2010 at 11:52 am
=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
December 27th, 2010 at 1:50 pm
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.
March 19th, 2011 at 2:54 am
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
March 21st, 2011 at 8:55 am
Shawna,
The array formula would be ridiculously large, too large I think.
March 21st, 2011 at 7:53 pm
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.
March 21st, 2011 at 8:14 pm
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!
March 22nd, 2011 at 2:42 pm
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!
November 30th, 2011 at 2:16 pm
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