find 2nd occurence
 

Hi all,
I have a row of dates like this:
10.03.2008
10.26.2008
11.16.2008
11.17.2008
12.16.2008
01.08.2009
......
on the same sheet there is a table with dates intervals like this one:
Time Interval Code
01.07.2008 - 03.19.2008 A
03.06.2008 - 03.18.2008 B
03.14.2008 - 06.30.2008 C
05.24.2008 - 09.12.2008 D
09.13.2008 - 11.17.2008 E
....
I was trying to find the first occurence of the dates from A:A in the
table of time intervals and get the code and actually i did, with this
formula in B:B :
=INDEX($J$3:$J$15,MATCH(1,((A2$K$3:$K$15)*(A2<$L$ 3:$L$15)),0))
and it works great,
Now i am trying to expand the formula to find the second occurence
(put it in C:C) different than the first one found in (B:B) and i got
stuck, i just cant see it, i am thinking that something should be done
inside my MATCH so to speak "start from where you stopped in B:B", can
this be done?
or otherwise any ides on how to approach it in an other way?
Thanks .

find 2nd occurence
 

On Nov 17, 12:20*pm, Totti wrote:
Hi all,
I have a row of dates like this:
10.03.2008
10.26.2008
11.16.2008
11.17.2008
12.16.2008
01.08.2009
.....
on the same sheet there is a table with dates intervals like this one:
Time Interval * * * * * * * *Code
01.07.2008 - 03.19.2008 A
03.06.2008 - 03.18.2008 B
03.14.2008 - 06.30.2008 C
05.24.2008 - 09.12.2008 D
09.13.2008 - 11.17.2008 E
...
I was trying to find the first occurence of the dates from A:A in the
table of time intervals and get the code and actually i did, with this
formula in B:B :
=INDEX($J$3:$J$15,MATCH(1,((A2$K$3:$K$15)*(A2<$L$ 3:$L$15)),0))
and it works great,
Now i am trying to expand the formula to find the second occurence
(put it in C:C) different than the first one found in (B:B) and i got
stuck, i just cant see it, i am thinking that something should be done
inside my MATCH so to speak "start from where you stopped in B:B", can
this be done?
or otherwise any ides on how to approach it in an other way?
Thanks .


I've done almost everything but still can't make it with the letter
code problems. In fact, the solution you suggest for the B:B, doesn't
work for me. Instead, I did it with IF but what if the time intervals
where 50 or even 100?

Can anyone help?

find 2nd occurence
 

I forgot to say that in K:K i have the starting date of the interval
=left(A2,10) and in J:J i have the end dates = right(A2,10)
and it works fine so if the date start date * date < end date, this
will give me "1" in the truth table and i am matching the 1, and
getting the code.

find 2nd occurence
 

On Nov 17, 1:45*pm, Totti wrote:
I forgot to say that in K:K i have the starting date of the interval
=left(A2,10) and in J:J i have the end dates = right(A2,10)
and it works fine so if the date start date * date < end date, this
will give me "1" in the truth table and i am matching the 1, and
getting the code.


So, if in K:K you have the starting date and in J:J the ending date, I
suppose that in L:L you have the letter codes? I understood what you
are trying to do, but this simply doesn't work all the way down to A:A
dates. I keep getting N/A till the cell corresponding to the last
interval, and then I get a VALUE error.

The IF thing, worked fine, but I am still not happy with that. I want
a simple formula, and not a 4-line one.

find 2nd occurence
 

Found:
=INDEX(OFFSET($J$3:$J$15,MATCH(1,((A2$K$3:$K$15)* (A2<$L$3:$L$15)),0),
0),MA*TCH(1,((A2OFFSET($K$3:$K$15,MATCH(1,((A2$K $3:$K$15)*(A2<$L$3:$L
$15)),0),0*))*(A2<OFFSET($L$3:$L$15,MATCH(1,((A2$ K$3:$K$15)*(A2<$L
$3:$L$15)),0),0))),*0))

Ste, what do you think?

find 2nd occurence
 

On Nov 17, 10:20*pm, Totti wrote:
Found:
=INDEX(OFFSET($J$3:$J$15,MATCH(1,((A2$K$3:$K$15)* (A2<$L$3:$L$15)),0),
0),MA*TCH(1,((A2OFFSET($K$3:$K$15,MATCH(1,((A2$K $3:$K$15)*(A2<$L$3:$L
$15)),0),0*))*(A2<OFFSET($L$3:$L$15,MATCH(1,((A2$ K$3:$K$15)*(A2<$L
$3:$L$15)),0),0))),*0))

Ste, what do you think?

After 3 hours of thinking, I tried to use something like the solution
you gave but with no results. Your solution seems to work, it has some
results. Nice thought!

find 2nd occurence
 

This formula can give you any nth occurrence:
=INDEX(Code,SMALL(IF((Fix R=Start)*(Fix R<=End),rown),VALUE(BinA C)))
For an Excel 2007 spreadsheet example see:
http://www.savefile.com/files/1891070