Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the row number of name occurence | Excel Worksheet Functions | |||
Find Last Occurence in a Range with VBA | Excel Discussion (Misc queries) | |||
Find first occurence of a number in an array 7 cols wide | Excel Worksheet Functions | |||
Trying to FIND lowercase or uppercase of target occurence | Excel Worksheet Functions | |||
Find first occurence in a list that's greater than a specific num | Excel Worksheet Functions |