Updating QtyReceived from other file

[Guest]

Dear Member,
I have two files : purchasing.xls and Receiving.xls.
In purchasing.xls there are fields: PO#, Vendor, PartNumber, QtyPurchase and
QtyReceived, while in Receiving.xls, there are fields
RRP#(ReceivingReportPart), PartNumber, PO#, QtyReceived

I would like to update the field QtyReceived in purchasing file with the
condition if the fields PartNumber and PO# in both files are identical.

So, I am able to get the BackOrder Report.

Please help me, to solve the above problem.

Thank you,
Andri

 

[RagDyeR]

I am assuming that both of your sheets start in column A, and labels are in
row1, and data is in A2:E100 and A2100 respectively.

The columns match *exactly* the order in which you have posted them here.

Enter this *array* formula in E2 of the "Purchasing" sheet:

=IF(ISNA(MATCH(A2&C2,Receiving!$C$1:$C$21&Receiving!$B$1:$B$21,0)),"NoMatch"
,INDEX(Receiving!$A$1:$D$100,MATCH(1,(Receiving!$C$1:$C$100=A2)*(Receiving!$
B$1:$B$100=C2),0),4))

As an array formula, it *must* be entered using CSE (<Ctrl> <Shift> <Enter>)
instead of just the regular Enter.
If done correctly, XL will *automatically* enclose the formula in curly
brackets.
This *cannot* be done manually.

Then the formula can be copied down as needed.

The formula will return a text "NoMatch" if no exact match is found.
If you prefer an empty cell instead, just remove the text from the formula,
leaving the quotes behind.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Dear Member,
I have two files : purchasing.xls and Receiving.xls.
In purchasing.xls there are fields: PO#, Vendor, PartNumber, QtyPurchase and
QtyReceived, while in Receiving.xls, there are fields
RRP#(ReceivingReportPart), PartNumber, PO#, QtyReceived

I would like to update the field QtyReceived in purchasing file with the
condition if the fields PartNumber and PO# in both files are identical.

So, I am able to get the BackOrder Report.

Please help me, to solve the above problem.

Thank you,
Andri

 

[Guest]

Dear Rag Dyer

Excellent, it works...but a lot of memory consumption due to i am comparing
2000 records in purchasing.xls, and 8000records in receiving.xls.

Thank you,
andri

 

[Ragdyer]

Thanks for the feed-back.

FWIW,
If you could live with your cells displaying the #N/A errors when the
pertinent data can't be found, you could perhaps eliminate the error
trapping portion of the formula, and then see if you realize any kind of
appreciable increase in efficiency.

=INDEX(Receiving!$A$1:$D$100,MATCH(1,(Receiving!$C$1:$C$100=A2)*(Receiving!$
B$1:$B$100=C2),0),4)