Need formula in excel for filtering large database

S

Sharkies

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...
 
P

Pete_UK

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),MINUTE(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
 
S

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 here: ....(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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top