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

=INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sheet2!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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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&Sheet2!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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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&Sheet2!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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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&Sheet2!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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>

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
>

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
> >

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
> > >

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
> > > >

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
> > > >

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
> > > > >