Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

hi,

I'd like to take a list of intervals defined by pairs of numbers, eg

3,5

2,4

9,12

10,11

15,17

and convert this into a consolidated list counting any overlapping intervals as one, so the above example would give:

2,5

9,12

15,17

See attached drawing that illustrates this.

I want to do this with array/matrix operations, if possible, so I can implement in google sheets without coding.

Thanks

diagram.jpg

I'd like to take a list of intervals defined by pairs of numbers, eg

3,5

2,4

9,12

10,11

15,17

and convert this into a consolidated list counting any overlapping intervals as one, so the above example would give:

2,5

9,12

15,17

See attached drawing that illustrates this.

I want to do this with array/matrix operations, if possible, so I can implement in google sheets without coding.

Thanks

diagram.jpg

No guarantees that Google sheets will be able to implement any particular solution that works in Excel.

I'll leave it to others.

Thanks,

Alan.

1) eliminate any number than falls within any other interval, giving:

2,

, 5

9,12

,

15,17

2) use concatenate & split functions to shuffle out the blanks:

2, 5

9,12

15,17

Here is a solution in google sheets: https://docs.google.com/spreadsheets/d/1-z7JtZndxSd2U7FuWkSW6YfT1dverEJo-dJLvCuXvto/edit#gid=0

If the input number pairs are in columns A & B, the formulae are:

(sorry a little messy - unfortunately google arrayformula is limited in handling logic!)

G3=if(sum(arrayformula(if(

H3=if(sum(arrayformula(if(

(formulae copied down)

Required result:

=transpose(split(concatena

=transpose(split(concatena

https://docs.google.com/spreadsheets/d/1-z7JtZndxSd2U7FuWkSW6YfT1dverEJo-dJLvCuXvto/edit#gid=339715318

If data is in cells A3:A20 and B3:B20 then the required output is given by the following 2 formulae:

Start =transpose(split(concatena

End =transpose(split(concatena

The google sheet has some notes breaking this down, though i wish formatted and commentable cell formulae were possible!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

2,4

3,5

9,12

10,11

15,17

Look at first two entries

2,4

3,5

3<4, throw away 3

5 > 4, throw away 4

leaving

2,5

Look at newly formed interval and compare with next entry:

2,5

9,12

9<5: false; keep 9; start new interval; and compare with next entry:

9,12

10,11

10<12, throw away 10

11 > 12: false, throw away 11

leaving:

9, 12

9,12

15,17

15<12: false, keep 15; start new interval; and compare with next entry:

no more entries

done

resulting in

2,5

9,12

15,17

I don't believe you can do this with standard matrix operations. But easy to do with code.