4
3
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?
Nam G VU
Posted 2012-03-24T11:30:49.727
Reputation: 10 255
10
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))))
Jerry Beaucaire
Posted 2012-03-24T11:30:49.727
Reputation: 495
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 – 2013-02-01T08:08:14.763
-1
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.
Bork Blatt
Posted 2012-03-24T11:30:49.727
Reputation: 334
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 – 2012-03-24T11:52:47.250
1I'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 – 2012-03-24T12:05:18.357
@BorkBlatt Yes, you got me. – Nam G VU – 2012-03-24T17:11:17.347
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 – 2012-03-24T19:44:39.083
@Jerry - absolute statements? "The example they linked to suggests... " is an absolute statement? – Bork Blatt – 2012-03-26T11:05:20.930
1No, 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 – 2012-03-26T15:19:34.907
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 – 2012-03-24T11:51:00.247
@Raystafarian Yes it is. – Nam G VU – 2012-03-24T17:11:04.320