Consolidating overlapping intervals

xenium
xenium used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AlanConsultant

Commented:
Hi Xenium,

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

Alan.

Author

Commented:
Potentially yes, if i can port it to an array-based solution in google sheets
AlanConsultant

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.
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

Author

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),","))

Author

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!

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial