data analysis
 

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

data analysis
 

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

data analysis
 

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

data analysis
 

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

data analysis
 

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

data analysis
 

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?

data analysis
 

Hi Nitya,

Am Tue, 12 Apr 2016 01:43:22 -0700 (PDT) schrieb Nitya Satheesh:

Is there any way I can get this output?

have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "CountOfDays"


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

data analysis
 

On Tuesday, April 12, 2016 at 2:40:54 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Tue, 12 Apr 2016 01:43:22 -0700 (PDT) schrieb Nitya Satheesh:

Is there any way I can get this output?

have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "CountOfDays"


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

Hi Claus,
So sorry for the late reply and thank you for your result.The code you have given me calculates the minimum period for each species. What I needed was the minimum period in which i get all the species together.I am so sorry i haven't been able to explain this better. Is there anyway i can modify the formula you have given me so that it encompasses the whole set of species?

Thank you,
Nitya

data analysis
 

Hi Nitya.

Am Thu, 14 Apr 2016 02:45:54 -0700 (PDT) schrieb Nitya Satheesh:

So sorry for the late reply and thank you for your result.The code you have given me calculates the minimum period for each species. What I needed was the minimum period in which i get all the species together.I am so sorry i haven't been able to explain this better. Is there anyway i can modify the formula you have given me so that it encompasses the whole set of species?

try:
=SMALL(A2:A100,SUMPRODUCT((B2:B100<"")/COUNTIF(B2:B100,B2:B100&""))*2)-SMALL(A2:A100,1)+1


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

data analysis
 

Hi Nitya,

Am Thu, 14 Apr 2016 12:10:00 +0200 schrieb Claus Busch:

try:
=SMALL(A2:A100,SUMPRODUCT((B2:B100<"")/COUNTIF(B2:B100,B2:B100&""))*2)-SMALL(A2:A100,1)+1

or use a Pivot table. That's more reliable. Have another look in
OneDrive


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

data analysis
 

On Thursday, April 14, 2016 at 4:12:12 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Thu, 14 Apr 2016 12:10:00 +0200 schrieb Claus Busch:

try:
=SMALL(A2:A100,SUMPRODUCT((B2:B100<"")/COUNTIF(B2:B100,B2:B100&""))*2)-SMALL(A2:A100,1)+1

or use a Pivot table. That's more reliable. Have another look in
OneDrive


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

Hi claus,
thank you so much for the result and sorry for the late reply again. I couldn't find it in one drive, I will try using a pivot table.

data analysis
 

Hi Nitya,

Am Mon, 25 Apr 2016 23:28:33 -0700 (PDT) schrieb Nitya Satheesh:

thank you so much for the result and sorry for the late reply again. I couldn't find it in one drive, I will try using a pivot table.

sorry, I load the file into a wrong folder.
Please look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "CountOfDays"


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

data analysis
 

On Tuesday, April 26, 2016 at 12:20:45 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Mon, 25 Apr 2016 23:28:33 -0700 (PDT) schrieb Nitya Satheesh:

thank you so much for the result and sorry for the late reply again. I couldn't find it in one drive, I will try using a pivot table.

sorry, I load the file into a wrong folder.
Please look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "CountOfDays"


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

Thanks Claus, that worked perfectly! Thank you for your time.