4

I have two columns of data in Excel. I would like to add a third column which combines the first and second. How can I do this with a formula such that I can add or remove data from columns A and B without ever having to touch column C?

Column A  Column B  Column C
Bob       Mary      Bob
Joe       Melissa   Joe
Jim       Jackie    Jim
                    Mary
                    Melissa
                    Jackie
porkfried42
  • 73
  • 1
  • 1
  • 5

5 Answers5

2

The question explicit mention Microsoft Office Excel but I think would be good to add that if you are using Google Sheets a simpler solution is to use the curly brackets function/operator as mentioned by Lake at https://stackoverflow.com/a/14151000/1802726.

enter image description here

Raniere Silva
  • 2,527
  • 1
  • 18
  • 34
1

Here is a simple solution using FILTERXML and TEXTJOIN that can append MULTIPLE RANGES OF ANY SIZE, ARRAY FORMULAS AND REGULAR FORMULAS. Just replace YOUR_RANGES with the ranges or dynamic arrays you wish to join:

Simple version that ignores empty cells:

=FILTERXML("<A><B>" & TEXTJOIN("</B><B>",TRUE,YOUR_RANGES) & "</B></A>", "//B")

This one includes empty cells:

=IFERROR(FILTERXML("<A><B>" & TEXTJOIN("</B><B>",FALSE,YOUR_RANGE) & "</B></A>", "//B"), "")

If your input data contains the "<" character, the formulas above will return an error, so use this one instead:

=IFERROR(SUBSTITUTE(FILTERXML("<A><B>" & SUBSTITUTE(SUBSTITUTE(TEXTJOIN("ΨΨ",FALSE,YOUR_RANGE),"<","ЉЉ"),"ΨΨ","</B><B>")&"</B></A>","//B"),"ЉЉ","<"),"")

Note: you can change the FALSE to TRUE to ignore empty cells.

Note 2: You can replace the characters ЉЉ and ΨΨ by any character(s). I used these specific characters because it is very unlikely that your input data will contain ЉЉ or ΨΨ, which would cause errors.

NOTES:

Tested on:

  • Excel 365

EXAMPLE:

enter image description here

Using the simple version of the formula:

=FILTERXML("<A><B>" & TEXTJOIN("</B><B>",TRUE,A1:A3,B1:B3,C1:C3) & "</B></A>", "//B")

As a result you will get a dynamic array with the joined/appended ranges:

enter image description here

You can then apply any dynamic array formula (like UNIQUE) to the result.

HOW THIS WORKS:

The JOINTEXT function grabs your ranges and joins them as a text with the delimiter "</ B >< B >". Then, after adding "< A >< B >" to the beginning and "</ B ></ A >" to the end, we have an XML formatted text:

<A><B>1</B><B>2</B><B>3</B><B>A</B><B>B</B><B>C</B><B>!</B><B>@</B><B>#</B></A>

Finally, the FILTERXML will separate the tags into a dynamic array which will be the joined/appended ranges.

cyberponk
  • 1,585
  • 18
  • 19
1

This thread is old but I had this problem today. Use VSTACK.

For the original question:

=VSTACK(A1:A3,B1:B3)

Hope that saves you some time.

MourningPajamas
  • 11
  • 2
0

Enter the following formula into cell C1

=IF(ROW()>COUNTA(A:B),"",IF(ROW()<=COUNTA(A:A),INDEX(A:A,ROW()),INDEX(B:B,ROW()-COUNTA(B:B))))

Then copy down as far as you need.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
0

Here's a nice way of interleaving the two rows.

In other words, turning this:

A  X
B  Y
C  Z

into this:

X
A
Y
B
Z
C

Say the above table is in columns one and two, you'd do:

=IF(MOD(ROW(),2)=0,INDIRECT(ADDRESS(INT(ROW()/2), 1)), 
 INDIRECT(ADDRESS(INT(ROW()/2)+1, 2)))

Explanation

Let's break that down a little. The first part is MOD(ROW(), 2) which returns a zero if the current row is even, and a one if it's odd.

So the IF goes FALSE/TRUE/FALSE/TRUE as we go down the column.

Next, the ADDRESS(INT(ROW()/2), 1) returns us a string representation of the address of the cell at column 1 and at half the current row. (Rounded down). This piece on its own looks like:

#VALUE!
$A$1
$A$1
$A$2
$A$2
$A$3
$A$3

(That first #VALUE error is because 1/2 = 0.5 which rounds down to zero. There's no row zero!)

The INDIRECT function returns whatever value is found at that address.

The rest is pretty clear.

NOTE: There may be a slicker way than using INDIRECT and ADDRESS. Suggestions welcome.

LondonRob
  • 73,083
  • 37
  • 144
  • 201