|
Merge data with Multiple-to-1 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 |
Merge data with Multiple-to-1 relationship
On Mar 5, 1:45*pm, fflores818 wrote:
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 |
Merge data with Multiple-to-1 relationship
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; "fflores818" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com