double match then find out item

[majidsiddique]

Hi all,
i am using a formula but did not get desired result. kindly help me.
see the attached file, highlighted are results but formula is not giving me OK result. there are little bit difference in items based on invoices and qty.
Thanks.

[Special-K]

Your VLOOKUP is failing, ie there is NO data on the "OK-DATA" sheet that begins "MD-NCEUP50" for 17-16-new!C5.
The same with the other N/A errors.

[majidsiddique]

Hi special-K,
please see again attached sheet. items are there in little bit differences. see the pic. actually i want to see the items are mismatch in these invoices.

[Special-K]

I'm gonna need a more verbose description of the problem as I cant see why youve highlighted those ones in green.

[majidsiddique]

Hi special-K,
please see the pic. its clear you every thing. if you have required more clarification please tell me.
Thanks.

[majidsiddique]

in this scenario some items are mostly close to each other BUT last word must be different, not possible to perfect match to each other.
See MD-NCEUP4006 last work in OK data sheet MD-NCEU4006X. THIS IS VERY DIFFICULT TO ME HOW TO SETTLE THIS ISSUE. same as top one.

[Special-K]

Sorry this problem seems to be quite complex and I don't have time to solve this as I am at work.
Hopefully someone else will be able to help you.

[Bo_Ry]

Please explain why you pick these value on each row, especially row 3, 6, 8, 11

Maj.jpg

[majidsiddique]

Hi Bo_Ry,
thanks for your reply,
actually theses reports collects from two different system. one is 17-18new sheet and other is OK-DATA sheet. i merge INV + ITEM concentrate formula and apply vlook(b2,ok-data!A2:A50000,4,0) to find KIP No, this main objective. OK-DATA sheet i remove most of column because file is too big so remove merge cell inv + Item in OK-DATA sheet. where this formula break the result is #N/A out of 10000 (item + invoice) cell. its mean 723 item + invoices are not match and did not find KIP No. then i copy only #N/A result in other sheet which i give you.
so i now i check and correct where is a missing in items. to find out KIP. when item will OK then Vlookup find KIP No. this is main problem.
currently i am working on this project. now i apply new formula in new sheet match invoice and item and pick accurate item from OK-DATA sheet, because i have to set 17-18new sheet.
if you have any other idea to solve this issue you can do that, move any column any where, you can do that.
one possiblity in my min d is:
if all 17-18new sheet invoices are match to OK-DATA sheet and remove all other invoices so then it will easy to identify item. OK-DATA sheet invoices are more than 33000.
please see new attached sheet.

[majidsiddique]

invoices can be deleted in OK-DATA sheet quickly with MACRO or other than macro.

[majidsiddique]

SUMIFS can give just ITEM from OK-DATAsheet after matching invoice and qty?

[majidsiddique]

can you tell me why conditional formatting is not working?

[AliGW]

This is a different issue - please start a new thread with a new thread title. Thanks.

[majidsiddique]

Hi AliG,
ok i start a new thread BUT actually this is a part of this.

[AliGW]

But6 it's about conditional formatting. This is different to what you were asking for at the start of the thread.

[Bo_Ry]

You still not explain why you pick each item from OK-DATA.

Eg row 6 Item is MD-NCEUP5015 but you pick MD-NCEU27515, while Data from OK-DATA has a closer match like MD-NCEU5015X, but still missing "P".

This is just 1 sample if other samples require different logic, this gonna be very hard to do.

[majidsiddique]

Hi,
actually both items are different in our inventory system, MD-NCEUP5015 and MD-NCEU5015X is different to each other. cost of both item is differ. KIP No tell me exact cost of item. if wrong item pick the cost will be wrong put. after this i have to add column COST of Item. so this is very important to pick right. i am stuck in this problem because entry are too many 723 invoices problem. manual working is too difficult to set filter and check each item.

[Bo_Ry]

KIP No tell me exact cost of item. if wrong item pick the cost will be wrong put.

All items in Post#16 are different but same KIP-No.

this is very important to pick right.

How do I know if I pick it right? why do you pick MD-NCEU27515 for MD-NCEUP5015, what are the criteria?

[majidsiddique]

Hi Bo_Ry,
criteria is simple, if KIP No is same cost will not change, no effect in costing, when KIP No change then cost change. you can pick any one item after matching Invoice and Qty. Invoice A10033 there are three item Qty 5, BUT KIP No is same, you can pick anyone of them. see the pic

[Bo_Ry]

Please try

Item
=LOOKUP(1,1/('OK-DATA'!$B$2:$B$1999=$E2)/('OK-DATA'!$D$2:$D$1999=$I2),'OK-DATA'!C$2:C$1999)

KIP
=LOOKUP(1,1/('OK-DATA'!$B$2:$B$1999=$E2)/('OK-DATA'!$D$2:$D$1999=$I2),'OK-DATA'!G$2:G$1999)

If QTY are the same but different KIP, then what are the criteria?

[majidsiddique]

please let me check

[majidsiddique]

can i upload 5 MB file?

[Bo_Ry]

Please don't upload 5 MB, we only need 20-30 rows of the sample.

[majidsiddique]

You are super, really fantastic job,
i never forget this.
Thanks a lot in my deep heart.
your question is valid, If QTY are the same but different KIP, then what are the criteria? i cannot give any criteria.
after putting the cost value then one more criteria will be there. some items are free of cost. so it is difficult to understand you.
kindly tell me how to add 2 more criteria in this formula.
=LOOKUP(1,1/('OK-DATA'!$B$2:$B$1999=$E2)/('OK-DATA'!$D$2:$D$1999=$I2),'OK-DATA'!C$2:C$1999)
i want add more than 2 criteria, how to add on it.
Add it,
('OK-DATA'!$C$2:$C$1999=$F2)/('OK-DATA'!$G$2:$G$1999=$M2) THEN PICK KIP No- from H:H.
Thanks again BOSS.

[Bo_Ry]

Like this

=LOOKUP(1,1/('OK-DATA'!$B$2:$B$1999=$E2)/('OK-DATA'!$D$2:$D$1999=$I2)/('OK-DATA'!$C$2:$C$1999=$F2)/('OK-DATA'!$G$2:$G$1999=$M2),'OK-DATA'!H$2:H$1999)

[majidsiddique]

Hi Bo_Ry,
Thanks for this.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.