Data Manipulation

I am creating a planning tool and need to be able to split my data to calculate completion times. The first table is how the data is received, the second is how I need to output the data. Can anybody suggest any way I can automate this, as doing this manually is very time consuming

224214 5X80 15 224155 5VY0 35
224318 5XAH 51 224237 5VY0 10
224312 50A20 15 224273 5VY0 15
224277 5-YA030 25 224415 5-0B70 30
224421 5-YA030 25 224506 5TNH 15
224510 5-YA030 25 224464 5-0B70 42
224575 5-YA030 25 224477 6GM0T 50
224339 5-YA030 50 224560 5MJH 15
224562 5-YA030 1 224561 5XVH 20


224214 5X80 15 224155 5VY0 15
224318 5XAH 20 224155 5VY0 20
224318 5XAH 10 224237 5VY0 10
224318 5XAH 15 224273 5VY0 15
224318 5XAH 6 224415 5-0B70 6
224312 50A20 15 224415 5-0B70 15
224277 5-YA030 9 224415 5-0B70 9
224277 5-YA030 15 224506 5TNH 15
224277 5-YA030 1 224464 5-0B70 1
224421 5-YA030 25 224464 5-0B70 25
224510 5-YA030 16 224464 5-0B70 16
224510 5-YA030 9 224477 6GM0T 9
224575 5-YA030 25 224477 6GM0T 25
224339 5-YA030 16 224477 6GM0T 16
224339 5-YA030 15 224560 5MJH 15
224339 5-YA030 19 224561 5XVH 19
224562 5-YA030 1 224561 5XVH 1