Merge data with Multiple-to-1 relationship

[fflores818]

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

 

[meh2030]

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

There are a few functions I can think of that may be of help to you.
Under the "Text" category of the function dialog box, you can use FIND
or SEARCH to get the first "-" position. You can then use LEFT to get
the text to the left of the first "-" position. You can then use this
as your "key" for looking up data that you want to compare against via
the VLOOKUP function. If you need to combine data you can use the
CONCATENATE function or simply the & (e.g. =A1&A2, ="From "&A1&" to
"&A2, etc.). Test these function out to see if it will give you what
you are looking for.

Matt

 

[KC]

Sample data seems to suggest:
each PO will have its unique tracking nr;
if there is n items in a PO,
each tracking nr can take m items;
then a multiple item PO will use up int(n/m)+1(mod(n,m)>0) tracking nr;