On Tuesday, April 12, 2016 at 11:06:05 AM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 11 Apr 2016 22:25:55 -0700 (PDT) schrieb Nitya Satheesh:

What I was looking for is the entire species set. In my above dataset, my first date is 1/1/2014 where i have got my first species and the last date would be 21/1/2014 where i have got species 5. so my time period would be 21 days. i just need a period in which i get all my species. i have 5 species in the above list, so the minimum time period in which i have got all my 5 species.

if you have a new Excel version try:
=MINIFS(A:A,B:B,"species 5")-MINIFS(A:A,B:B,"species 1")+1
With older versions try:
=INDEX(A:A,SMALL(IF(B1:B1000="species 5",ROW(1:1000)),1))-INDEX(A:A,SMALL(IF(B1:B1000="species 1",ROW(1:1000)),1))+1
and enter the last formula with CRTL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus!
Thanks a lot!This worked!,the only thing is in my data set species 5 can come int he beginning too like how i have shown below..

col1 col2
1/1/2014 species 1
1/1/2015 species 5
2/1/2014 species 1
2/1/2014 species 3
3/1/2014 species 2
4/1/2014 species 1
8/1/2014 species 1
8/1/2014 species 3
15/1/2014 species 3
15/1/2014 species 4
21/1/2014 species 5
11/2/2014 species 1
26/2/2014 species 4

so what i need is the minimum time period in which i get all the species (species 1, species 2 , species 3 , species 4 and species 5). so here my output would be 15 days. i have got species 1 and 5 on 1/1/14, species 2 on 3/1/2014 , species 3 on 2/1/2014 and species 4 on 15/1/2014. so my time period would be between 1/1/2014 and 15/1/2014. so the minimum time period i which i get a unique list of all species.

I am really sorry I haven't been able to explain it better, and thank you so much for your time.

Thanks
Nitya