Consolidating overlapping intervals

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
xeniumAsked:
Who is Participating?
 
phoffricCommented:
I guess you already know how to do this with code, but for those who might want to know, here is one approach, using your numbers:
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.
0
 
AlanConsultantCommented:
Hi Xenium,

You have tagged for Excel, so would you accept an Excel solution?

Alan.
0
 
xeniumAuthor Commented:
Potentially yes, if i can port it to an array-based solution in google sheets
0
Ultimate Tool Kit for Technology Solution Provider

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.

 
AlanConsultantCommented:
Hi,

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.
0
 
xeniumAuthor Commented:
Thanks, i think the following maybe equivalent, and although possibly not with matrices, an arrayformula solution in google sheets should work:

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
0
 
xeniumAuthor Commented:
Thanks all for your input.

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((A3>$A$3:$A$999) & (A3<$B$3:$B$999)="TRUETRUE",1,0)))>0,"",A3&",")
H3=if(sum(arrayformula(if((B3>$A$3:$A$999) & (B3<$B$3:$B$999)="TRUETRUE",1,0)))>0,"",B3&",")
(formulae copied down)                            

Required result:
=transpose(split(concatenate(G3:G10),","))
=transpose(split(concatenate(H3:H10),","))
0
 
xeniumAuthor Commented:
Here is a version with just arrays (no formulae copied down), it's quite convoluted, but does the job:

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(concatenate(arrayformula(if(mmult(arrayformula(if(($A$3:$A$20>transpose($A$3:$A$20))&($A$3:$A$20<transpose($B$3:$B$20))="TRUETRUE",1,0)),transpose(split(rept("1 ",rows(A3:A20))," ")))=1,"",$A$3:$A$20 & ","))),","))

End   =transpose(split(concatenate(arrayformula(if(mmult(arrayformula(if(($B$3:$B$20>transpose($A$3:$A$20))&($B$3:$B$20<transpose($B$3:$B$20))="TRUETRUE",1,0)),transpose(split(rept("1 ",rows(A3:A20))," ")))=1,"",$B$3:$B$20 & ","))),","))

The google sheet has some notes breaking this down, though i wish formatted and commentable cell formulae were possible!
0
 
xeniumAuthor Commented:
PS there is a bug with my solution above it does not work if the end of an interval exactly coincides with the end of another interval.

However the bug is fixed by wrapping the formulae within the function UNIQUE
0
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.