How to write in vba a search code by closest values

[edupaulo]

I need help with a code in vba. I have a table like this:

Date Hour n TotalHour TotalMin Flow pressure
10/07/2016 00:02:55 873 13111 12 38 7
10/07/2016 00:07:55 903 13111 17 38 7
10/07/2016 00:12:55 933 13111 22 38 8
10/07/2016 00:17:55 943 13111 27 38 7
10/07/2016 00:22:55 943 13111 32 38 7
10/07/2016 00:27:55 943 13111 37 38 7
10/07/2016 00:32:55 963 13111 42 38 7
10/07/2016 00:37:55 993 13111 47 38 7
10/07/2016 00:42:56 23 13111 52 38 7
10/07/2016 00:47:56 53 13111 57 38 7
… … … … … … ...

OBS: The times do not follow a pattern.

So you can see, this table have different schedules. But I need just some schedules, they are: 00:00:00, 00:15:00, 00:30:00, 00:45:00, 01:00:00, 01:15:00,……,23:00:00, 23:15:00, 23:30:00, 23:45:00, 00:00:00,….., for each day of month. How it’s not possible, I have to take the schedules closest to these possible times and after to delete the other lines that do not interest me. Therefore the only lines that stay are: 1st, 4th, 7th, 10th, and so on. How I can to write the vba code for this problem, because there is no logical or comparison operator to do this search and to delete the schedules the other lines that not interest me.

Thanks for the help! And sorry for the english, I'm still learning.

[Pete_UK]

Your times are increasing in 5 minute increments, so that means that you only want every third record starting from the first record. Your comparison could thus be:

IF row_number - 1 MOD 3 = 0 ...

(this is not necessarily a VBA statement, but it shows how you can determine which record to retain).

Hope this helps.

Pete

[edupaulo]

This table is only part of the table that I have. The times do not follow a pattern. Sometimes change the seconds, sometimes change de minutes.

[Pete_UK]

Okay, I think you should choose which of the two threads you want to continue in, so we don't have to keep duplicating our efforts.

If you understand how VLOOKUP( ... , TRUE) works, then you will find the highest value which is less than or equal to the lookup_value. You also want to find the next value in the sequenceso that you can compare the two, but VLOOKUP doesn't allow you to do that very easily, so it is better to use an INDEX/MATCH combination, where you add 1 onto the MATCH value. You can subtract each of these from the lookup_value to find out which is the closer one.

Hope this helps.

Pete

[edupaulo]

I'll try Pete. Thanks. But i'm still believe more easy to find a vba code that's contains logical or comparison operator to do this, because I have many spreadsheets inside another worksheet. Each spreadsheets contains 6 to 12 months of data, where only a day contains 96 schedules.

Thanks for the help.