Dataset combination - Dates, Streets, Routes

**Jack_Smith2** · 05-18-2011, 12:58 AM

Hi All

Thanks in advance for any assistance you can provide.

The Problem:
I am trying to create a table (Table 3) from two other tables (1 and 2).

Table1: describes traffic routes (ie vehicles driving from A to B), the date the vehicles start and stop, and the amount of vehicles per day that travel along the route. Eg. Between the 1/1/2012 and 4/1/2012, 10 vehicles per day travel on Route1.

Table2: describes the streets that are on the route. Eg. When a vehicle drives on Route1, it drives on Street1, Street2 and Street3

Table3: provides a summary of streets, the date and the total amount of traffic on the streets on those particular dates. I have filled in the correct answers in the attached example so you can see when the table should end up.

What I'm wanting is a way for Excel to calculate Table 3, either by formulas or VBA code in the most efficient manner.

Part of the problem is the size of the data set. My example is only very small. The real data set includes every day for 24 years, approximately 200 routes and over 120 streets

I've been trying to solve this for ages and have come to the end of the road - so aka I'm asking for help. This might end up as a VBA script - I think that would be more efficient as the quantum of data is just huge.

Thanks again for any assistance.

**TonyB51** · 05-18-2011, 01:55 AM

Hi,

I have worked with your sheet.

Yes, it was trickie but came up with a result.

It will require some modification to expand.

See what you think.

Cheers

TonyB

**Jack_Smith2** · 05-18-2011, 02:03 AM

Thanks for having a look Tony. The problem I see is the quantum of data in the real data set. To calculate every day for 24 years gives 8,760 columns per street. With 120 streets, I would need 1,051,200 columns!

Yes it's a tricky one as initially it looks so easy, but without using individual columns it becomes interesting lol. I'm thinking that it be really sweet to upload the data to a VBA array, then code the array to check dates, routes and streets, then export the array to a sheet or seperate csv file - but my VBA programming skills aren't up to scratch!!