# Problem matching two different sets of intervals

#### excelnoober

##### New Member
Well I just signed in to ask the following question as I have spent a day thinking and trying things with no results (no superuser experience!)

I have two datasets:

The first one, A has a start and an end and 0/1 values for values A,B,C,D (note, for each row there can be only one 1 and all others are always 0). Intervals at A dataset are pretty much random and can intersect between them or have large gaps.

The second one B, starts from 0 (or sometimes from a positive value) and each record finishes right where the next starts. The problem is to fill with "1" the yellow area as in the posted photo that it was filled manually, meaning each row interval in dataset A will fill the corresponding row with 1.

For example the first row "20 to 40" is contained in the "15,8 to 25,83" and "25,83 to 43,69" so "1" has to be filled in the corresponding value 2 column

I have a corresponding formula to correspond a single A dataset row but I have to do this around 3000 times! If anyone had an idea how to do this would be extremely helpful. If VBA solutions would be avoided would be great, as I only know that you could do magic things with this stuff but I have zero knowledge about it...

Thanks for the time spent reading this post!

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Domenic

##### MrExcel MVP
Let's assume that we have the following data...

 A start A end Value 1 Value 2 Value 3 Value 4 20 40 0 1 0 0 75 90 1 0 0 0 80 100 1 0 0 0 55 105 0 0 0 1

<COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY>
</TBODY>

What is the expected result for "58,84 to 90,6"?

#### excelnoober

##### New Member

Well in the interval "58,84 to 90,6" of B dataset there is overlap the "80-100" Value1, (from 80 to 90,6 actually), "75-90" also value1 (from 70 to 90 actually) and 55- 105 value4 (from 58.84 to 90,6 actually). "1" values do not have to be added, but if they are there isn't a great deal in changing them

So the yellow area will be completed as following...

#### Domenic

##### MrExcel MVP
Try...

M5, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=(SUM(IF(((\$K5>=\$D\$5:\$D\$8)*(\$K5<=\$E\$5:\$E\$8))+((\$L5>=\$D\$5:\$D\$8)*(\$L5<=\$E\$5:\$E\$8))+((\$K5<\$D\$5:\$D\$8)*(\$L5>\$E\$5:\$E\$8)),INDEX(\$F\$5:\$I\$8,0,MATCH(M\$4,\$F\$4:\$I\$4,0))))>0)+0

Hope this helps!

#### excelnoober

##### New Member
Thanks a lot!!!! It simpy works!!!! You are simply the Man!!!

(You can't believe how much time you saved!!!!)

Cheers!

Replies
4
Views
440
Replies
14
Views
957
Replies
5
Views
258
Replies
3
Views
377
Replies
0
Views
321

1,191,671
Messages
5,987,956
Members
440,121
Latest member
eravella

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back