| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Calculates Qty Received
Dear All,
Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#2
|
|||
|
|||
|
=INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sh eet2!B2:B100,0))
which is an array formula, so commit with Ctrl-Shift-Enter RP (remove nothere from the email address if mailing direct) "Andri" wrote in message ... Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#3
|
|||
|
|||
|
Dear Bob,
The result of the formula still 1ea instead of 4ea. due to that formula only find the first ROW(second parameter in index formula) Sheet2!C2:C100. Please advice... brgds,andri "Bob Phillips" wrote: =INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sh eet2!B2:B100,0)) which is an array formula, so commit with Ctrl-Shift-Enter RP (remove nothere from the email address if mailing direct) "Andri" wrote in message ... Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#4
|
|||
|
|||
|
Sorry, I don't understand. Where does 1ea and 4ea figure in all this?
-- HTH RP (remove nothere from the email address if mailing direct) "Andri" wrote in message ... Dear Bob, The result of the formula still 1ea instead of 4ea. due to that formula only find the first ROW(second parameter in index formula) Sheet2!C2:C100. Please advice... brgds,andri "Bob Phillips" wrote: =INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sh eet2!B2:B100,0)) which is an array formula, so commit with Ctrl-Shift-Enter RP (remove nothere from the email address if mailing direct) "Andri" wrote in message ... Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#5
|
|||
|
|||
|
Dear Bob,
Please notice in sheet2, there are three times of receiving period of the parts purchased. So this is the scenario, we bought 6ea (Quantity Ordered), and have received 3 times (1ea, 2ea, 1ea) in different RECEIVED date. So the purpose to know, there is BACK ORDER of 2ea. Thank you "Bob Phillips" wrote: Sorry, I don't understand. Where does 1ea and 4ea figure in all this? -- HTH RP (remove nothere from the email address if mailing direct) "Andri" wrote in message ... Dear Bob, The result of the formula still 1ea instead of 4ea. due to that formula only find the first ROW(second parameter in index formula) Sheet2!C2:C100. Please advice... brgds,andri "Bob Phillips" wrote: =INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sh eet2!B2:B100,0)) which is an array formula, so commit with Ctrl-Shift-Enter RP (remove nothere from the email address if mailing direct) "Andri" wrote in message ... Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#6
|
|||
|
|||
|
I think your looking for in the qty purchased column
Sumproduct((sheet2_range of part numbers=sheet1partnumber)* (sheet2_range of dates=sheet1date)*(sheet2_range of quantity)) Example =SUMPRODUCT(--(Sheet2!A1:A6=Sheet1!A14)*--(Sheet2!B1:B6=Sheet1!B14)*(Sheet2!C1:C6)) Note: all ranges must be of the same length a1:a6 b1:b6 etc LAnce "Andri" wrote: Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#7
|
|||
|
|||
|
Dear Lance,
I dont think i would like to calculate by formula "Sumproduct". The purpose to know back order (Qty Purchase - Oty Received) in this case 6ea - 4ea, so back order 2ea. let we say, we buy certain item, with PO# and Part Number with quantity 6ea. it reflected in sheet1. We have received partial shipment of that order. There are 3 shipment with the following qty 1ea + 2ea + 1ea = 4ea.It reflected in sheet2. I would like to get the RECEIVED QTY by MATCH the PO# AND PartNUMBER in sheet1 and sheet2. Thank you and need you further advice "LanceB" wrote: I think your looking for in the qty purchased column Sumproduct((sheet2_range of part numbers=sheet1partnumber)* (sheet2_range of dates=sheet1date)*(sheet2_range of quantity)) Example =SUMPRODUCT(--(Sheet2!A1:A6=Sheet1!A14)*--(Sheet2!B1:B6=Sheet1!B14)*(Sheet2!C1:C6)) Note: all ranges must be of the same length a1:a6 b1:b6 etc LAnce "Andri" wrote: Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#8
|
|||
|
|||
|
Andri
It is difficult to be sure what you are looking at, but sumproduct is one of the most powerful functions in Excel. It allows you to count or sum items with multiple criteria. It is used to look thru a list of data and return as an example the quanity of all products of a unique number recieved on a certain date from a list of multiple products and dates. If you need to compare that to products sold you could simply subtract that result from a sumproduct function returning the the sold products. You don't need to respond to this if I'm missing your point, just wanted to make sure you were not overlooking this feature. "Andri" wrote: Dear Lance, I dont think i would like to calculate by formula "Sumproduct". The purpose to know back order (Qty Purchase - Oty Received) in this case 6ea - 4ea, so back order 2ea. let we say, we buy certain item, with PO# and Part Number with quantity 6ea. it reflected in sheet1. We have received partial shipment of that order. There are 3 shipment with the following qty 1ea + 2ea + 1ea = 4ea.It reflected in sheet2. I would like to get the RECEIVED QTY by MATCH the PO# AND PartNUMBER in sheet1 and sheet2. Thank you and need you further advice "LanceB" wrote: I think your looking for in the qty purchased column Sumproduct((sheet2_range of part numbers=sheet1partnumber)* (sheet2_range of dates=sheet1date)*(sheet2_range of quantity)) Example =SUMPRODUCT(--(Sheet2!A1:A6=Sheet1!A14)*--(Sheet2!B1:B6=Sheet1!B14)*(Sheet2!C1:C6)) Note: all ranges must be of the same length a1:a6 b1:b6 etc LAnce "Andri" wrote: Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#9
|
|||
|
|||
|
Dear Lance,
Thank you for the advice. Yes you are right, i have never use this function before. So will follow your advice and i will try to study it in details. Thank you. Andri "LanceB" wrote: Andri It is difficult to be sure what you are looking at, but sumproduct is one of the most powerful functions in Excel. It allows you to count or sum items with multiple criteria. It is used to look thru a list of data and return as an example the quanity of all products of a unique number recieved on a certain date from a list of multiple products and dates. If you need to compare that to products sold you could simply subtract that result from a sumproduct function returning the the sold products. You don't need to respond to this if I'm missing your point, just wanted to make sure you were not overlooking this feature. "Andri" wrote: Dear Lance, I dont think i would like to calculate by formula "Sumproduct". The purpose to know back order (Qty Purchase - Oty Received) in this case 6ea - 4ea, so back order 2ea. let we say, we buy certain item, with PO# and Part Number with quantity 6ea. it reflected in sheet1. We have received partial shipment of that order. There are 3 shipment with the following qty 1ea + 2ea + 1ea = 4ea.It reflected in sheet2. I would like to get the RECEIVED QTY by MATCH the PO# AND PartNUMBER in sheet1 and sheet2. Thank you and need you further advice "LanceB" wrote: I think your looking for in the qty purchased column Sumproduct((sheet2_range of part numbers=sheet1partnumber)* (sheet2_range of dates=sheet1date)*(sheet2_range of quantity)) Example =SUMPRODUCT(--(Sheet2!A1:A6=Sheet1!A14)*--(Sheet2!B1:B6=Sheet1!B14)*(Sheet2!C1:C6)) Note: all ranges must be of the same length a1:a6 b1:b6 etc LAnce "Andri" wrote: Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#10
|
|||
|
|||
|
Dear Lance,
Thank you very much. It is an amazing formula to solve my problem. And definitely your advice is highly appreciated. Case Closed....:):):) "LanceB" wrote: Andri It is difficult to be sure what you are looking at, but sumproduct is one of the most powerful functions in Excel. It allows you to count or sum items with multiple criteria. It is used to look thru a list of data and return as an example the quanity of all products of a unique number recieved on a certain date from a list of multiple products and dates. If you need to compare that to products sold you could simply subtract that result from a sumproduct function returning the the sold products. You don't need to respond to this if I'm missing your point, just wanted to make sure you were not overlooking this feature. "Andri" wrote: Dear Lance, I dont think i would like to calculate by formula "Sumproduct". The purpose to know back order (Qty Purchase - Oty Received) in this case 6ea - 4ea, so back order 2ea. let we say, we buy certain item, with PO# and Part Number with quantity 6ea. it reflected in sheet1. We have received partial shipment of that order. There are 3 shipment with the following qty 1ea + 2ea + 1ea = 4ea.It reflected in sheet2. I would like to get the RECEIVED QTY by MATCH the PO# AND PartNUMBER in sheet1 and sheet2. Thank you and need you further advice "LanceB" wrote: I think your looking for in the qty purchased column Sumproduct((sheet2_range of part numbers=sheet1partnumber)* (sheet2_range of dates=sheet1date)*(sheet2_range of quantity)) Example =SUMPRODUCT(--(Sheet2!A1:A6=Sheet1!A14)*--(Sheet2!B1:B6=Sheet1!B14)*(Sheet2!C1:C6)) Note: all ranges must be of the same length a1:a6 b1:b6 etc LAnce "Andri" wrote: Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
|
#11
|
|||
|
|||
|
Thanks for the feedback
Lance "Andri" wrote: Dear Lance, Thank you very much. It is an amazing formula to solve my problem. And definitely your advice is highly appreciated. Case Closed....:):):) "LanceB" wrote: Andri It is difficult to be sure what you are looking at, but sumproduct is one of the most powerful functions in Excel. It allows you to count or sum items with multiple criteria. It is used to look thru a list of data and return as an example the quanity of all products of a unique number recieved on a certain date from a list of multiple products and dates. If you need to compare that to products sold you could simply subtract that result from a sumproduct function returning the the sold products. You don't need to respond to this if I'm missing your point, just wanted to make sure you were not overlooking this feature. "Andri" wrote: Dear Lance, I dont think i would like to calculate by formula "Sumproduct". The purpose to know back order (Qty Purchase - Oty Received) in this case 6ea - 4ea, so back order 2ea. let we say, we buy certain item, with PO# and Part Number with quantity 6ea. it reflected in sheet1. We have received partial shipment of that order. There are 3 shipment with the following qty 1ea + 2ea + 1ea = 4ea.It reflected in sheet2. I would like to get the RECEIVED QTY by MATCH the PO# AND PartNUMBER in sheet1 and sheet2. Thank you and need you further advice "LanceB" wrote: I think your looking for in the qty purchased column Sumproduct((sheet2_range of part numbers=sheet1partnumber)* (sheet2_range of dates=sheet1date)*(sheet2_range of quantity)) Example =SUMPRODUCT(--(Sheet2!A1:A6=Sheet1!A14)*--(Sheet2!B1:B6=Sheet1!B14)*(Sheet2!C1:C6)) Note: all ranges must be of the same length a1:a6 b1:b6 etc LAnce "Andri" wrote: Dear All, Please find the first sheet as follows: PO# Part Number Qty Purchased Qty Received 4734LA004 3-1439-6 6 by formula? While the second sheet as follows: PO# Part Number Qty Received Date of Received 4734LA004 3-1439-6 1 1Mar05 4734LA004 3-1439-6 2 10Mar05 4734LA004 3-1439-6 1 1 April05 how is the right coding for formula : to get the result in Sheet 1, column Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match function, still not able to solve... the Match Criteria: that PO# AND ("&") Part Number is must be equal between sheet 1 and sheet 2. Need your help, plz :) Brgds,andri |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel only calculates the confidence interval for a population . | Excel Worksheet Functions | |||
| Is there a time sheet template that calculates hours? | Excel Discussion (Misc queries) | |||
| Is there a time sheet template that calculates hours? | Excel Discussion (Misc queries) | |||
| Saving Received Excel Attachments | Excel Discussion (Misc queries) | |||
| Is there a way to link a received outlook email to a cell? | Excel Worksheet Functions | |||
Linear Mode
