# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ConsultantCommented:
Hi Xenium,

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

Alan.
0
Author Commented:
Potentially yes, if i can port it to an array-based solution in google sheets
0
ConsultantCommented:
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
\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.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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

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
Author 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!
0
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
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Consulting

From novice to tech pro — start learning today.