Working with mileages

OK, I dont think this can be solved with excel formulas, it may need a macro. I hope I am wrong and that someone can point me in the right direction:

Imagine, if you will that there are some roads named road1 - road5 (to keep things simple), and that each road has a mileage. These roads are walked on a number of days between certain mileages, thus you get the following data:

Day RoadNum StartMileage EndMileage
1 Road1 23.5 25
1 Road2 12 14
1 Road4 13 15
2 Road2 13 15
2 Road1 20 25

Now, the problem. I need to know the last time each part of a road was covered and the day. So from the above data I would get:

RoadNum Start End Day
Road1 20 25 2
Road2 12 13 1
Road2 13 15 2
Road4 13 15 1

Does this make sense? Is it possible? I think not, but I live in hope.

What happened to the following row?

Day--RoadNum--StartMileage--EndMileage
1------Road1------23.5----------25

It does not appear in your results table, yet Road2 appears twice (once for each day). I do not follow the logic.

the reason the row is missing is that that mileage was covered on day two, and both day 1 and day 2 mileages are unique on road two.

The mileages are not a length, they are a start and end point, i.e. road 1 could run from 1 mile to 30 miles, road 2 from 12 miles to 70 miles etc.

That is the basic problem, i want to create table two, that contains the extents of the roads that have been covered without duplicate coverage.