asked on

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

Alan

Hi Xenium,

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

Alan.

xenium

ASKER

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

Alan

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.

ASKER CERTIFIED SOLUTION

phoffric

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

xenium

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xenium

ASKER

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

xenium

ASKER

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!

xenium

ASKER

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