filtering data
 

Hi !
My spreadsheet looks a little like the following

a 2:10:43 4/10/15
a 2:15:34 4/10/15
a 2:45:23 4/10/15
a 4:45:34 5/10/15
a 6:40:15 6/10/15
a 3:09:56 7/10/15
a 1:23:22 8/10/15
a 2:45:06 8/10/15
a 2:20:54 8/10/15
a 4:10:31 8/10/15

So I need to treat every 'a' that has occurred in the period of an hour of the same day as one event. How do I filter my data to show this.

Thanks in advance !

Nitya

filtering data
 

Hi Nitya,

Am Tue, 23 Jun 2015 01:03:36 -0700 (PDT) schrieb Nitya Satheesh:

a 2:10:43 4/10/15
a 2:15:34 4/10/15
a 2:45:23 4/10/15
a 4:45:34 5/10/15
a 6:40:15 6/10/15
a 3:09:56 7/10/15
a 1:23:22 8/10/15
a 2:45:06 8/10/15
a 2:20:54 8/10/15
a 4:10:31 8/10/15

So I need to treat every 'a' that has occurred in the period of an hour of the same day as one event. How do I filter my data to show this.

insert headers to your columns. Then write in D2: =1
In D3 write:
=IF(AND(A3=A2,C3=C2,HOUR(B3)=HOUR(B2)),0,1)
and copy down. Then filter column D by 1


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

filtering data
 

On Tuesday, June 23, 2015 at 1:54:28 PM UTC+5:30, Claus Busch wrote:
Hi Nitya,

Am Tue, 23 Jun 2015 01:03:36 -0700 (PDT) schrieb Nitya Satheesh:

a 2:10:43 4/10/15
a 2:15:34 4/10/15
a 2:45:23 4/10/15
a 4:45:34 5/10/15
a 6:40:15 6/10/15
a 3:09:56 7/10/15
a 1:23:22 8/10/15
a 2:45:06 8/10/15
a 2:20:54 8/10/15
a 4:10:31 8/10/15

So I need to treat every 'a' that has occurred in the period of an hour of the same day as one event. How do I filter my data to show this.

insert headers to your columns. Then write in D2: =1
In D3 write:
=IF(AND(A3=A2,C3=C2,HOUR(B3)=HOUR(B2)),0,1)
and copy down. Then filter column D by 1


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

Thanks CLaus!

Can I write if(C3=c2,if(b3-b2)<time(1,0,0),1,0),0)
Will this also work ?

filtering data
 

Hi Nitya,

Am Tue, 23 Jun 2015 02:06:05 -0700 (PDT) schrieb Nitya Satheesh:

Can I write if(C3=c2,if(b3-b2)<time(1,0,0),1,0),0)

why didn't you test it?

you can use:
=IF(C3=C2,IF(B3-B2<=TIME(1,,),0,1),1)
=IF(AND(C3=C2,B3-B2<=TIME(1,,)),0,1)
=IF(AND(C3=C2,HOUR(B3)=HOUR(B2)),0,1)
=IF(AND(C3=C2,B3-B2<=TIME(1,,)),0,1)

But I would prefer formula 3. This formula check if the times are in one
full hour.
The other formulas are not reliable depending on the times you have.
Test them and decide by yourself what formula gives you the expected
result.


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

filtering data
 

Hi Nitya,

Am Tue, 23 Jun 2015 02:06:05 -0700 (PDT) schrieb Nitya Satheesh:

Can I write if(C3=c2,if(b3-b2)<time(1,0,0),1,0),0)
Will this also work ?

please look here
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for the workbook "Events"
I highlighted one row yellow. IMO this event is a new event but only one
formula has 1 as result.


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