Problem matching two different sets of intervals

[excelnoober]

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!

 

[Domenic]

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]

Thanks for replying,

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]

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]

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

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

 

[Domenic]

That's great, your very welcome!

Cheers!