Excel 2007 : Heavy data manipulation with multiple outputs possible for IF statements

**donkey1** · 04-20-2012, 07:01 PM

Hi everybody.

I'm new to VB and this forum but not a noob to forums. I've tried to search but I'm not sure the keywords and my problem is somewhat complicated.

I have a huge Excel data file that I need to manipulate to output the information I'm looking for. The data is ridership data for several bus routes. The interested column headings are Date, Timestamp and Bus Route. I also have an Excel file of the bus schedules.

What I need to do, I will explain by using an example with a sample of the data provided below.

Sample Data:

Date Timestamp Bus Route
8/26/2011 1:09 PM JC
8/26/2011 11:54 PM LRSS
8/27/2011 3:54 PM JC
8/27/2011 12:18 AM TC
8/28/2011 1:28 AM LRSS
8/28/2011 10:48 AM TC

Bus Schedules:
JC Schedule
Leaves Union Returns
7:00 AM 7:40 AM
7:20 AM 8:00 AM
7:40 AM 8:20 AM
8:00 AM 8:40 AM
8:20 AM 9:00 AM
8:40 AM 9:20 AM
9:00 AM 9:40 AM
9:20 AM 10:00 AM
9:40 AM 10:20 AM
10:00 AM 10:40 AM
10:20 AM 11:00 AM
10:40 AM 11:20 AM
11:00 AM 11:40 AM
11:20 AM 12:00 PM
11:40 AM 12:20 PM
12:00 PM 12:40 PM
12:30 PM 1:10 PM
1:00 PM 1:40 PM
1:30 PM 2:10 PM
2:00 PM 2:40 PM
2:30 PM 3:10 PM
3:00 PM 3:40 PM
3:30 PM 4:10 PM
4:00 PM 4:40 PM
4:30 PM 5:10 PM
5:00 PM 5:40 PM
5:30 PM 6:10 PM
6:00 PM 6:40 PM
6:40 PM 7:20 PM
7:00 PM 7:40 PM
7:40 PM 8:20 PM
8:00 PM 8:40 PM
8:40 PM 9:20 PM
9:00 PM 9:40 PM
9:40 PM 10:20 PM
10:00 PM 10:40 PM
10:40 PM 11:20 PM
11:00 PM 11:40 PM
11:40 PM 12:20 AM
12:00 AM 12:40 AM

TC Schedule
Leaves Dickinson Returns
6:20 PM 6:55 PM
7:20 PM 7:55 PM
8:20 PM 8:55 PM
9:20 PM 9:55 PM
10:20 PM 10:55 PM
11:20 PM 11:55 PM
12:20 AM 12:55 AM
1:20 AM 1:55 AM
2:20 AM 2:55 AM
3:20 AM 3:55 AM

LRSS Schedule
Leaves Union Returns
6:00 PM 6:43 PM
7:00 PM 7:43 PM
8:00 PM 8:43 PM
9:00 PM 9:43 PM
10:00 PM 10:43 PM
11:00 PM 11:43 PM
12:00 AM 12:43 AM
1:00 AM 1:43 AM
2:00 AM 2:43 AM
3:00 AM 3:43 AM

From the sample data you can see that somebody got on the JC bus at 1:09 PM. The problem with the data provided is that it doesn't tell me which scheduled bus that specific Timestamp belongs to, good thing is I don't care. If you look at the schedule for the JC bus, you can see that there are two intervals that the Timestamp can belong to, 12:30PM - 1:10PM and 1PM - 1:40PM. From checking the extremities, I know that Timestamps can belong to to either 0, 1 or 2 scheduled buses. What I want to do is output (the time that the bus leaves) which scheduled buses the Timestamp could belong to. There are timestamps that don't belong to any scheduled bus because somebody could have gotten on a bus that was early or late. I want to output the scheduled bus time that those Timestamps are closer to.

Please keep in mind that there are 3 bus routes and I need to make sure which Timestamps belong to which route.

I want to do this in VB because I need this to be a sustainable program. If there are new data points, all the user has to do is copy and paste the designated columns to the respective columns in the correct locations that the VB will read.

Does that make sense? lol