I have a large database (25000 rows) and would like to apply a filter to
it. I have 4 fields (variables) with 25000 rows (readings every second).
What I'm trying to attempt is to filter out the database at minute
intervals, that is, picking one minute per set of rows (a number of
readings per minute), choosing the closest reading to the exact minute
(e.g. 7:46:03). The problem here is that the set of readings per minute
vary from one to 20 and I need to pick only one. Is there a function or
formula in excel that you recommend trying to filter my data??
Thanks...




--
Sharkies

Assuming that your times are in column A starting with A2, and that
they are in sequence, put this formula in a helper column next to your
data (eg in E2):

=IF(TIME(HOUR(A2),MINUTE(A2),0)<TIME(HOUR(A1),MIN UTE(A1),0),"yes","")

Copy this down, and you will get a series of blank cells and cells
with "yes" - these occur at every change of minutes. You can now apply
a filter to column E to get what you want - select "yes" from the
filter pull-down.

Hope this helps.

Pete

On Mar 28, 6:37*am, Sharkies
wrote:
I have a large database (25000 rows) and would like to apply a filter to
it. I have 4 fields (variables) with 25000 rows (readings every second).
What I'm trying to attempt is to filter out the database at minute
intervals, that is, picking one minute per set of rows (a number of
readings per minute), choosing the closest reading to the exact minute
(e.g. 7:46:03). The problem here is that the set of readings per minute
vary from one to 20 and I need to pick only one. Is there a function or
formula in excel that you recommend trying to filter my data??
Thanks...

--
Sharkies

Hi Pete,

Thanks for the prompt reply. Just one problem. I tried using the
formula but there seems to be a problem with it because excel will try
to correct by inseting a * sign he ....(a2)minute(a2),0) *
time(hour(a1),minute(a1)....
Is there something missing in the formula? Here's an example of the
type of data that I have:

t1 p1 lat lon Time
15.4 54.9 29.14508 -118.28356 12:52:47
15.5 55.6 29.14512 -118.28358 12:53:12
15.5 55.6 29.14516 -118.28356 12:53:24
15.6 56.4 29.14516 -118.28358 12:53:28
15.5 55.7 29.14517 -118.28358 12:53:32
15.4 37 29.1452 -118.28358 12:53:44
15.7 18.7 29.1452 -118.2836 12:53:52
15.5 55.5 29.14839 -118.28132 12:56:43
15.4 55.9 29.1484 -118.28132 12:56:47
15.4 56.7 29.14841 -118.28132 12:56:55
15.5 56.7 29.14842 -118.28133 12:57:03
15.6 57.1 29.14843 -118.28132 12:57:07
15.5 57.4 29.14844 -118.28131 12:57:11
15.4 58.5 29.14844 -118.28131 12:57:15
15.4 59.7 29.14845 -118.28131 12:57:19
15.5 60.1 29.14845 -118.28131 12:57:23




Pete_UK;2695278 Wrote:
Assuming that your times are in column A starting with A2, and that
they are in sequence, put this formula in a helper column next to your
data (eg in E2):

=IF(TIME(HOUR(A2),MINUTE(A2),0)TIME(HOUR(A1),MINUT E(A1),0),"yes","")

Copy this down, and you will get a series of blank cells and cells
with "yes" - these occur at every change of minutes. You can now apply
a filter to column E to get what you want - select "yes" from the
filter pull-down.

Hope this helps.

Pete

On Mar 28, 6:37 am, Sharkies
wrote:-
I have a large database (25000 rows) and would like to apply a filter
to
it. I have 4 fields (variables) with 25000 rows (readings every
second).
What I'm trying to attempt is to filter out the database at minute
intervals, that is, picking one minute per set of rows (a number of
readings per minute), choosing the closest reading to the exact
minute
(e.g. 7:46:03). The problem here is that the set of readings per
minute
vary from one to 20 and I need to pick only one. Is there a function
or
formula in excel that you recommend trying to filter my data??
Thanks...

--
Sharkies-




--
Sharkies