Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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?

share|improve this question
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

2 Answers

up vote 3 down vote accepted

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

enter image description here

share|improve this answer
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 at 8:08
up vote 0 down vote

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.

share|improve this answer
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
1  
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
1  
@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
1  
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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.