#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ste Ste is offline
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ste Ste is offline
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ste Ste is offline
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,180
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find the row number of name occurence [email protected] Excel Worksheet Functions 9 October 3rd 08 08:34 AM
Find Last Occurence in a Range with VBA jlclyde Excel Discussion (Misc queries) 2 October 1st 08 07:04 PM
Find first occurence of a number in an array 7 cols wide Ricardo-SA Excel Worksheet Functions 3 April 5th 08 11:22 PM
Trying to FIND lowercase or uppercase of target occurence u473 Excel Worksheet Functions 3 August 23rd 07 11:08 PM
Find first occurence in a list that's greater than a specific num stevep Excel Worksheet Functions 2 December 9th 05 09:41 PM


All times are GMT +1. The time now is 04:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"