Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Get the second Occurence of something
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)) in J:J i have the Codes (AA.......XX) in K:K i have the starting date of the interval =left(A2,10) and in L:L i have the end dates = right(A2,10) 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 ideas on how to approach it in an other way? Thanks . |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Get the second Occurence of something
Totti,
=INDEX(OFFSET($J$3:$J$15,MATCH(1,((A2$K$3:$K$15)* (A2<$L$3:$L$15)),0),0),MATCH(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)) Array entered, of course. Though this part (which appears three times) MATCH(1,((A2$K$3:$K$15)*(A2<$L$3:$L$15)) could go into its own cell and be referenced. HTH, Bernie MS Excel MVP "Totti" wrote in message ... 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)) in J:J i have the Codes (AA.......XX) in K:K i have the starting date of the interval =left(A2,10) and in L:L i have the end dates = right(A2,10) 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 ideas on how to approach it in an other way? Thanks . |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Get the second Occurence of something
This will find the *last* instance. Note that if there is only one instance
that will be both the first and the last instance! =LOOKUP(2,1/((A2K3:K15)*(A2<L3:L15)),J3:J15) -- Biff Microsoft Excel MVP "Totti" wrote in message ... 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)) in J:J i have the Codes (AA.......XX) in K:K i have the starting date of the interval =left(A2,10) and in L:L i have the end dates = right(A2,10) 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 ideas on how to approach it in an other way? Thanks . |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Get the second Occurence of something
Thank you both,
Bernie this seems to do its job great, Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find 2nd occurence | Excel Discussion (Misc queries) | |||
Copying down row to the next occurence in a different row. | Excel Discussion (Misc queries) | |||
next occurence | Excel Worksheet Functions | |||
Occurence #'s | Excel Discussion (Misc queries) | |||
get 2nd occurence value | Excel Discussion (Misc queries) |