Consolidating numerical ranges in Excel

[rschley]

I have columns of number ranges in Excel, some of which are sequential to each other. Example:

1-3
4-5
5-9

Is there a way to programmatically merge them such that the resulting range becomes:

1-9

Thanks!

Rebecca

 

[VoG]

Do you want to do this using native Excel formulas or a VBA User Defined Function?

 

[rschley]

I would prefer a native Excel formula.

 

[rschley]

Though if there is a VBA way to do that, I would like to know it as well.

Thanks!

 

[VoG]

With a couple of helper columns

Excel Workbook
	A	B	C	D
1	1-3	1	3	1-9
2	4-5	4	5
3	5-9	5	9
Sheet12

 

[rschley]

What if there is a break in the ranges?

1-3
4-5
6-9
25-27
27-29

and the desired end result is

1-9
25-29

Thanks!

Rebecca