Hi !
My data looks like this

col1 col2
1/1/2014 species 1
2/1/2014 species 1
2/1/2014 species 2
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 5

And so on...

I need to figure out a way to get the time period in which I have got all the species. For eg in the above dataset, I get all 5 species by 21/1/2014.
species1- 1/1/2014
species 2 - 2/1/2014
species 3 -8/1/2014..

My dataset is huge so i cannot do it manually, i basically need the period in which i get a unique list of the complete species set.

Please help!
Thanks in advance!

Nitya

Hi Nitya,

Am Mon, 11 Apr 2016 21:57:41 -0700 (PDT) schrieb Nitya Satheesh:

col1 col2
1/1/2014 species 1
2/1/2014 species 1
2/1/2014 species 2
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 5

I hope I understood your problem.
The first date for species 1:
=MINIFS(A:A,B:B,"species 1")
The last date similiar:
=MAXIFS(A:A,B:B,"species 1")

If that is not the solution, please post the expected output here.


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

On Tuesday, April 12, 2016 at 10:41:15 AM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 11 Apr 2016 21:57:41 -0700 (PDT) schrieb Nitya Satheesh:

col1 col2
1/1/2014 species 1
2/1/2014 species 1
2/1/2014 species 2
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 5

I hope I understood your problem.
The first date for species 1:
=MINIFS(A:A,B:B,"species 1")
The last date similiar:
=MAXIFS(A:A,B:B,"species 1")

If that is not the solution, please post the expected output here.


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

Hi Claus!

Thanks a lot for your reply !
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.

I hope i have explained better this time.

Thanks again.
Nitya

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

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

On Tuesday, April 12, 2016 at 12:02:18 PM UTC+5:30, Nitya Satheesh wrote:
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

Is there any way I can get this output?