data matching

[Jefflee]

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.

 

[Jacob Skaria]

Use SUMIF()

=SUMIF(Sheet1!B:B,"292500-00802S",Sheet1!D)
OR
=SUMIF(Sheet1!B:B,<cell reference>,Sheet1!D)


If this post helps click Yes

 

[Joel]

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]

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

 

[Jefflee]

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.