Consolidating overlapping intervals

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® is a registered trademark of EXPERTS EXCHANGE®
Consultant

Commented:
Hi Xenium,

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

Alan.

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

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

Commented:
Thanks all for your input.

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

Commented:
Here is a version with just arrays (no formulae copied down), it's quite convoluted, but does the job:

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!

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

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