Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data matching
Hi, I have a problem of matching data with different rows or records ??
Eg. CUSPO PRTNO ITDSC ORQTY 550927V 292500-00802S MOTOR ASSY, BLOWER 200 551111K 292500-00802S MOTOR ASSY, BLOWER 80 551330K 292500-00802S MOTOR ASSY, BLOWER 80 551331K 292500-00802S MOTOR ASSY, BLOWER 80 s/no PRTNO SHDQY 1 292500-00802S 100 2 292500-00802S 100 3 292500-00802S 40 4 292500-00802S 40 5 292500-00802S 20 6 292500-00802S 30 7 292500-00802S 30 8 292500-00802S 10 9 292500-00802S 40 10 292500-00802S 30 as above are the customer order data and delivery sales data. any ideas how to do matching ??? Thanks. -- llm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
data matching
Use SUMIF()
=SUMIF(Sheet1!B:B,"292500-00802S",Sheet1!D:D) OR =SUMIF(Sheet1!B:B,<cell reference,Sheet1!D:D) If this post helps click Yes --------------- Jacob Skaria "Jefflee" wrote: Hi, I have a problem of matching data with different rows or records ?? Eg. CUSPO PRTNO ITDSC ORQTY 550927V 292500-00802S MOTOR ASSY, BLOWER 200 551111K 292500-00802S MOTOR ASSY, BLOWER 80 551330K 292500-00802S MOTOR ASSY, BLOWER 80 551331K 292500-00802S MOTOR ASSY, BLOWER 80 s/no PRTNO SHDQY 1 292500-00802S 100 2 292500-00802S 100 3 292500-00802S 40 4 292500-00802S 40 5 292500-00802S 20 6 292500-00802S 30 7 292500-00802S 30 8 292500-00802S 10 9 292500-00802S 40 10 292500-00802S 30 as above are the customer order data and delivery sales data. any ideas how to do matching ??? Thanks. -- llm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
data matching
Sorry for the misleading,
my asking is how to give the po number under "wanted" based on cust order data given ?? PRTNO SHDQY WANTED 1 292500-00802S 100 550927V 2 292500-00802S 100 550927V 3 292500-00802S 40 551111K 4 292500-00802S 40 551111K 5 292500-00802S 20 551330K 6 292500-00802S 30 551330K 7 292500-00802S 30 551330K 8 292500-00802S 10 551331K 9 292500-00802S 40 551331K 10 292500-00802S 30 551331K -- llm "Jacob Skaria" wrote: Use SUMIF() =SUMIF(Sheet1!B:B,"292500-00802S",Sheet1!D:D) OR =SUMIF(Sheet1!B:B,<cell reference,Sheet1!D:D) If this post helps click Yes --------------- Jacob Skaria "Jefflee" wrote: Hi, I have a problem of matching data with different rows or records ?? Eg. CUSPO PRTNO ITDSC ORQTY 550927V 292500-00802S MOTOR ASSY, BLOWER 200 551111K 292500-00802S MOTOR ASSY, BLOWER 80 551330K 292500-00802S MOTOR ASSY, BLOWER 80 551331K 292500-00802S MOTOR ASSY, BLOWER 80 s/no PRTNO SHDQY 1 292500-00802S 100 2 292500-00802S 100 3 292500-00802S 40 4 292500-00802S 40 5 292500-00802S 20 6 292500-00802S 30 7 292500-00802S 30 8 292500-00802S 10 9 292500-00802S 40 10 292500-00802S 30 as above are the customer order data and delivery sales data. any ideas how to do matching ??? Thanks. -- llm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
data matching
There is purchase software packes that arre designed to perform these
function and they are not cheap becasue the algorithms "CAN" be complicated. the formula can become simplier if you use a FIFO (First in - first out ) algorithm which means the first order placed is the first order delivered. I donn't know if tthese orders arre for standard products or custom products. custtom products should be asign an order number when the order is received to make the tracking easier. Another issue is if the items are all at the same location. Then you don't wan't to split the order and want to find fill the order from one location. Again, can the orders be split? This can be another Greek packing Problem that the Greeks couldn't easily solve 2000 years ago. The greeks going to war was trying to figure the best way of loading up their chariots and weer having problem s with over loading and the chariots would tip over or move too slow, or not be filled up enough and require more chariots. They also had different size objects and was it better to fill one chariot with all small items and then put the big items in seperate chariots or to mix the different size objects. Yo have the same type problem that you have different size orders and different size lots you are producing and which is the best fit. And I'm not considering that some of your customers have priority over other customers. Do you think we learned any thing in 2000 years. Yes we have. We now have computers that can try every combination of packing and using some algorithm determine the best fit. "Jefflee" wrote: Hi, I have a problem of matching data with different rows or records ?? Eg. CUSPO PRTNO ITDSC ORQTY 550927V 292500-00802S MOTOR ASSY, BLOWER 200 551111K 292500-00802S MOTOR ASSY, BLOWER 80 551330K 292500-00802S MOTOR ASSY, BLOWER 80 551331K 292500-00802S MOTOR ASSY, BLOWER 80 s/no PRTNO SHDQY 1 292500-00802S 100 2 292500-00802S 100 3 292500-00802S 40 4 292500-00802S 40 5 292500-00802S 20 6 292500-00802S 30 7 292500-00802S 30 8 292500-00802S 10 9 292500-00802S 40 10 292500-00802S 30 as above are the customer order data and delivery sales data. any ideas how to do matching ??? Thanks. -- llm |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
data matching
Hi,
tks for the advise. But is there any ways to match these two set of data with different rows ?? to return customer p/o based on po qty as FIFO basis ?? s/no PRTNO SHDQY return 1 292500-00802S 100 550927V 2 292500-00802S 100 550927V 3 292500-00802S 40 551111K 4 292500-00802S 40 551111K 5 292500-00802S 20 551330K 6 292500-00802S 30 551330K 7 292500-00802S 30 551330K 8 292500-00802S 10 551331K 9 292500-00802S 40 551331K 10 292500-00802S 30 551331K Thanks. -- llm "Joel" wrote: There is purchase software packes that arre designed to perform these function and they are not cheap becasue the algorithms "CAN" be complicated. the formula can become simplier if you use a FIFO (First in - first out ) algorithm which means the first order placed is the first order delivered. I donn't know if tthese orders arre for standard products or custom products. custtom products should be asign an order number when the order is received to make the tracking easier. Another issue is if the items are all at the same location. Then you don't wan't to split the order and want to find fill the order from one location. Again, can the orders be split? This can be another Greek packing Problem that the Greeks couldn't easily solve 2000 years ago. The greeks going to war was trying to figure the best way of loading up their chariots and weer having problem s with over loading and the chariots would tip over or move too slow, or not be filled up enough and require more chariots. They also had different size objects and was it better to fill one chariot with all small items and then put the big items in seperate chariots or to mix the different size objects. Yo have the same type problem that you have different size orders and different size lots you are producing and which is the best fit. And I'm not considering that some of your customers have priority over other customers. Do you think we learned any thing in 2000 years. Yes we have. We now have computers that can try every combination of packing and using some algorithm determine the best fit. "Jefflee" wrote: Hi, I have a problem of matching data with different rows or records ?? Eg. CUSPO PRTNO ITDSC ORQTY 550927V 292500-00802S MOTOR ASSY, BLOWER 200 551111K 292500-00802S MOTOR ASSY, BLOWER 80 551330K 292500-00802S MOTOR ASSY, BLOWER 80 551331K 292500-00802S MOTOR ASSY, BLOWER 80 s/no PRTNO SHDQY 1 292500-00802S 100 2 292500-00802S 100 3 292500-00802S 40 4 292500-00802S 40 5 292500-00802S 20 6 292500-00802S 30 7 292500-00802S 30 8 292500-00802S 10 9 292500-00802S 40 10 292500-00802S 30 as above are the customer order data and delivery sales data. any ideas how to do matching ??? Thanks. -- llm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching a column of new data to existing larger data set. | Excel Worksheet Functions | |||
Extract matching data from large data file (csv) | Excel Programming | |||
Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
Can I merge data in 2 sheets matching rows of data by last name? | Excel Discussion (Misc queries) | |||
Matching data and linking it to the matching cell | Links and Linking in Excel |