Manipulate Excel Data (scripting required)

General information for the business: Data Manipulation
Industry: Technology
Description of excel work: I need to consolidate multiple rows for 1 bus into one row and then delete the old rows. My vehicle table has 1 row per bus per month. What I am trying to do is change the file so it contains only one row per bus with the monthly data in new columns.

The current columns are as follows (many rows per bus). The underlined columns are static/repeating for each monthly bus row. In bold are the columns that do change per bus per month :-


FLEET NO
REGISTRATION NO
VEHICLE TYPE
MODEL
COMPANY
ALLOCATED DEPOT
CLOSING ODOMETER
ODOMETER DAT
ODOMETER UNIT
FUEL USED IN PERIOD
MILES IN PERIOD
BSOG ELIGIBILITY


What I would like to do is have the data re-arranged as per the following columns with 1 row per bus:- (Essentially looking for the process to do this, we have a huge number or rows to process.)


FLEET NO
REGISTRATION NO
VEHICLE TYPE
MODEL
COMPANY
ALLOCATED DEPOT
ODOMETER UNIT
BSOG ELIGIBILITY
CLOSING ODOMETER1
ODOMETER DAT1
FUEL USED IN PERIOD1
MILES IN PERIOD1
CLOSING ODOMETER2
ODOMETER DAT2
FUEL USED IN PERIOD2
MILES IN PERIOD2
....
....
....
CLOSING ODOMETERn
ODOMETER DATn
FUEL USED IN PERIODn
MILES IN PERIODn

It would be great if I could also order the repeating 'n' group of columns chronologically . Is this possible? The ODOMETER_DAT is the date field to lookup on and is in dd/mm/yyyy format.

The monthly data for the buses is not consistent i.e. the buses could have anywhere from one months data (i.e. 1 row) to a years worth (i.e. 12 rows). FLEETNO is the unique identifier for the bus.

To summarise I need the data to be restructured to include:-

1. One row per bus
2. 8 fixed columns at the start of the row
3. A group of 4 monthly repeating columns driven by the number of rows per bus in the original data
4. Repeating columns to be ordered chronological based on ODOMETER DAT
5. If a bus does not have the max number of monthly rows then repeating columns can be blank

The attached file is:-

JP_Example - The upper table is how the data is currently structured, the bottom table how I would like it structured.
Extra notes: Looking for a script or formula to do the manipulation which can be run over a large dataset.