Time matching and layered column graphs

[SIDO-UK]

Hi,

I'm new to Excel but keen to learn. I've collected a range of information on a radar device to establish the volume and speed of passing vehicles during a 24/7 surveillance period across seven days. The on-board computer delivers the data in the following format:

28 11:19:42 26/06/2012 28 11:19:43 26/06/2012
36 11:19:57 26/06/2012 36 11:19:57 26/06/2012
23 11:20:28 26/06/2012 22 11:20:29 26/06/2012
31 11:24:49 26/06/2012 31 11:24:49 26/06/2012
31 11:24:50 26/06/2012 31 11:24:50 26/06/2012
34 11:26:45 26/06/2012 34 11:26:45 26/06/2012
37 11:28:12 26/06/2012 37 11:28:12 26/06/2012
35 11:28:41 26/06/2012 35 11:28:41 26/06/2012
24 11:31:16 26/06/2012 20 11:31:17 26/06/2012
36 11:33:36 26/06/2012 35 11:33:37 26/06/2012
10 11:39:06 26/06/2012 10 11:39:06 26/06/2012
31 11:39:57 26/06/2012 31 11:39:59 26/06/2012
41 11:42:19 26/06/2012 41 11:42:19 26/06/2012
43 11:42:56 26/06/2012 43 11:42:56 26/06/2012

- where the 1st-3rd columns are entry speed, time, date, and the 4th-6th columns are exit speed, time and date.

I'd like to transfer this to a column-graph where the entries are distributed along a continuous timeline (hour, minute, seconds on x-axis), and the highest of the recorded mph (whether entry or exit speed) displayed on the y-axis. Further, I want to include all seven days marked with different colors successively behind each other to identify identical traffic patterns during the week.

Could anyone with a lot more insight into the use of macros please help me achieve this?

[Steffen Thomsen]

Then i would start by creating a new sheet where you mix the entry and exit data and sort is by time, something like this.'

Please Login or Register  to view this content.

Should the graph be created with code or manually?
If it's something you'll only do once, then i'll suggest manually.

Else try using the macro recorder built into excel and then play around with the code it gives you.

[SIDO-UK]

Hi Steff,

Thanks for this.

I figured out how the function of determining the highest number of two columns can be done with a simple SUM - MAX formula. My biggest problem is to automate the import and distribution of the time entries (and the affiliated MAX values) along a continuous timeline set with 10 seconds intervals.

Any good ideas?

[Steffen Thomsen]

Hi,

I suggest you upload a sample workbook with dummy data and then i'll try and help you.

[SIDO-UK]

Thanks Steff,

The attached SAMPLE file has a full 24 hour time column incrementing by 10 seconds on Sheet 1. On Sheet 2 you'll find three speed entries (entry speed, exit speed, and MAX of the two). I'd like to automatically distribute the data from Sheet 2 into Sheet 1 in a manner where only the MAX speed appears within the correct 10 seconds span on the continuous timeline.

If it doesn't make sense I'll do a few allocations manually in a new file to exemplify the desired result.