How to merge two Excel columns into one?

Let's say we have two columns/ranges `A1:A10` (group A) and `B5:B50` (group B) and we need to merge the two into one column in `C1:C60` (60 in C = 10 from A + 50 from B)

Data in group A & group B can go up/down unpredictable.

I google and found this trick but that works on constant number of cells of each group.

How would we merge such dynamic ranges quickly?

-
What is the issue? Is it that you want new entries into list 1 to arrive above the list 2 entries in the new list? –  Raystafarian Mar 24 '12 at 11:51
@Raystafarian Yes it is. –  Nam G VU Mar 24 '12 at 17:11

This is the simplest way I would do that with just a formula.... if the values are in columns A and B, then this formula in C1, then copied down as far as you want, it will show blanks when the values run out:

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

-
This is great :-). Note that the formula assumes that there are no blank cells in column A: For example, if A3 was empty, COUNTA(A:A) would return 3 and the function would therefore ignore the data in A4. –  Jonas Heidelberg Feb 1 '13 at 8:08

There is no built in function to automate this for you - you will need to get someone to write an Excel-VBA macro to achieve it for you.

-
It'd be no problem to write a macro that combines list 1 and list 2 and assign a button to it, that way OP can run the macro every time the lists change, we just need to know how OP wants the data displayed.. –  Raystafarian Mar 24 '12 at 11:52
I'm glad you're willing and able to do this for the poster - I'm being a bit lazy today and just pointing in the right direction. I agree the spec is a bit hazy - though the example they linked to suggests that they just want to copy column A as is (no headers) and place column B immediately below. –  Bork Blatt Mar 24 '12 at 12:05
@BorkBlatt Yes, you got me. –  Nam G VU Mar 24 '12 at 17:11
@Bork, that is not the case. I would caution against making absolute statements like this. See alternate suggestion using INDEX(), ROW() and COUNTA() functions. –  Jerry Beaucaire Mar 24 '12 at 19:44
No, your answer was an absolute statement, the answer this comment is connected to. "There is no built in function to automate this for you - you will need to get someone to write an Excel-VBA macro to achieve it for you." I simply cautioned this as a strong statement as a first response to this. I caution you, nothing more. –  Jerry Beaucaire Mar 26 '12 at 15:19