Merge data with 9-to-4 relationship

Hi.
I have to modify the data before it is feeded into my accounting system

Each line of my sales order report represent one line of the purchase order. The PO can repeat itself as many times as there are line in it.

When these orders are processed in the UPS computer, a file is generated with a UPS tracking number. This file may be shorter, or some very strange situations be larger, than the original sales order.
It will be most of the cases shorter because some of these lines can be actually be shipped together in one box. Some times more than two items (or lines) in one box. A relation of multiple-to-1.

Very seldom, there will be cases where there is only one line with multiple quantites (like 10 for example) This 10 items will have to be split in, let say 3 boxes. That will be 3 tracking numbers. A relation of 1-to-multiple.

Is there a way to have something done in excell to check for the number of total lines of a PO# and merge the tracking numbers from another file?
The PO # will be commun field in both files.
Example:
Sales Order File
PO1-ITEM1-QTY1-NAME-etc..
PO2-ITEM2-QTY1-NAME-etc..
PO2-ITEM3-QTY1-NAME-etc..
PO2-ITEM4-QTY1-NAME-etc..
PO2-ITEM5-QTY1-NAME-etc...
PO3-ITEM6-QTY1-NAME-etc...
PO3-ITEM7-QTY1-NAME-etc...
PO4-ITEM8-QTY1-NAME-etc...
PO5-ITEM9-QTY1-NAME-etc...

Tracking # file
PO1-TRACKING1
PO2-TRACKING2
PO2-TRACKING3
PO3-TRACKING4
PO4-TRACKING5
PO5-TRACKING6

Final Combined File
PO1-ITEM1-QTY1-NAME-etc..TRACKING1
PO2-ITEM2-QTY1-NAME-etc..TRACKING2
PO2-ITEM3-QTY1-NAME-etc..TRACKING2
PO2-ITEM4-QTY1-NAME-etc..TRACKING3
PO2-ITEM5-QTY1-NAME-etc..TRACKING3
PO3-ITEM6-QTY1-NAME-etc..TRACKING4
PO3-ITEM7-QTY1-NAME-etc..TRACKING4
PO4-ITEM8-QTY1-NAME-etc..TRACKING5
PO5-ITEM9-QTY1-NAME-etc..TRACKING6

The key here i guess is to be able to see how many lines the PO has and how many lines were generated with the tracking # and be able to distribute the tracking number evenly among all the lines form the sales order.
When number of sales order lines is odd and the number or resulting line from the tracking # is even or viseversa, there is not rule of which line takes more or less of the same tracking #.
I hope this is well explained.
I have a sample of a full report with sample data to play with.
I will really appreciate the help you can provide.
Thank you

Here are the two files that need to be merged.
Thank you so much for the help!
Francisco

Here is the final file.
The extra data (Invoice, batch, etc) is actually added manually. That is ok.
But that is how the file should look like before it is feeded into the system.

Again, the sales order file can have, let say 7 lines; right?
The UPS system generate a files with only 4, meaning that 7 items were packed and shipped in 4 boxes. The relationships will 7-4.
In the files I sent, the first order has 10 lines and it was packed and shippedin 5 boxes, so what I did was to have the same tracking numbers for each two line. If the order has 7 lines and was shipped in 4 boxes i would then put one tracking number for the each pair of lines and leave one tracking # for the last line.

X X X X X X X = Lines
## ## ## ## ## ## # = tracking #'s

Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!