Combining named ranges

**goels** · 05-09-2011, 05:13 AM

Hi All,

I have two vertical named ranges which I want to combine into one column. So the data is as follows:

NamedRange1:

20
30
40
<Blank>
<Blank>

NamedRange2:

10
5
9
19
20
<Blank>
<Blank>
<Blank>

The combined output will look like NamedRange3 with no blanks:

20
30
40
10
5
9
19
20

Any suggestions on how this can be done preferably using Excel array functions?

Thanks

**goels** · 05-09-2011, 05:14 AM

Oh btw I am using Excel 2010

**jaslake** · 05-09-2011, 02:53 PM

Hi goels
See if this does what you described

Please Login or Register  to view this content.

PS: I believe I misread your request...ah well...throw it away.

**WHER** · 05-09-2011, 08:21 PM

If you make the first two named ranges dynamic (=no more blanks) then you could try this approach.

**goels** · 05-11-2011, 02:06 PM

Hi WHER,

It took me some time to readjust the formulas to my spreadsheet but it worked beautifully in the end. Thanks a lot for this

**muchkipachi** · 02-27-2012, 01:45 PM

Very nice. But what if you you want to combine more than 2 dynamic vertical ranges that are located in discontinuous columns?

Thanks
M

**ashish4654** · 11-18-2021, 02:21 AM

Hey, Isn't there any ways where I don't need to use all the nested formulas but simply use reference of previously defined ranges(Range1 & Range 2) to create (CombinedRange) and get it done using +unique(CombinedRange) in office 365?

**janmorris** · 11-18-2021, 04:34 AM

Originally Posted by ashish4654

Hey, Isn't there any ways where I don't need to use all the nested formulas but simply use reference of previously defined ranges(Range1 & Range 2) to create (CombinedRange) and get it done using +unique(CombinedRange) in office 365?

the method you are thinking of will only return UNIQUE values, and is done like this:

Please Login or Register  to view this content.

**ashish4654** · 11-18-2021, 05:50 AM

Hey,can you tell me what wrong did I do here? Individual Name ranges have scope all over the workbook.

**AliGW** · 11-18-2021, 06:20 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread