Need help with excel formula!

[Carol Su]

I need an excel formula advise, below is the scenario.

I have 2 tables here,

for table 1- the fields are order number, quantity, and price, part number.

For table 2- the fields are order number, quantity, price and stock code.

Assuming there are same orders found in table 1 & 2, quantity could be different , and the price could be Slightly different. And 1 order could have a few parts. I need to match the most possible parts in table 1 to the stock code in table 2 by looking at order level, then narrow down to nearest /same quantity then further narrow down to nearest / same price in order to get the stock code in table 2.

Kindly advice the possible formula that I could use to solve this problem. Thanks!!!

 

[NBVC]

Can you post a sample excel workbook, showing your setup and expected results, with explanation as required?

 

[Carol Su]

Workbook as attached for you reference.

Please find attached excel workbook for your reference.

Table 1 - My Record Table 2 - Invoice Received From Vendor
PO Number Quantity Total price Expected Results PO Number Quantity Total Price Stock Code
10001 1 750.00 2147138 10001 2 696.10 AAA-01
10001 1 177.22 DDD-99 10001 1 1,738.96 DDD-99
10001 2 710.51 AAA-01 10001 2 108.28 CXC-790
10001 2 110.54 CXC-790 10001 2 35.02 333_A
10001 2 40,922.90 2395815 10001 2 35.02 333_B
10001 2 36.50 333_A 10001 1 733.48 2147138
10001 2 36.50 333_B 10001 2 40,090.04 2395815
10002 2 717.52 BB_IM3 10002 4 1746.4 BB_IM3
10002 4 1,746.40 BB_IM3 10002 2 717.52 BB_IM3
10003 1 32.62 A8899 10003 1 32.62 A8899
10004 2 710.51 #N/A Total 45,933.44
10005 2 90.00 #N/A
10006 2 100.00 #N/A
Total 46,141.22

Note: For order 10001, Stock Code "333_A" & "333_B" having the same quantity and same price, I want to distribute this part to match the items in Table 1
Explaination:
I need to match the stock code to the most possible item in Table 1 so that when I make payment to the vendor, I am confident that I am paying to the correct quantity and amount, and I can avoid double payment to the same items.
So the criteria I would like to set up is to match the order, follow by the quantity, then follow by the closest possible total price in order to get the stock code.

 

[Carol Su]

The table is not outlined properly, i will re-post the table here. Fore detailed description, please find excel file as attached in previous reply.

Table 1 - My Record
PO Number	Quantity	Total price	Expected Results
10001	1	750.00	2147138
10001	1	177.22	DDD-99
10001	2	710.51	AAA-01
10001	2	110.54	CXC-790
10001	2	40,922.90	2395815
10001	2	36.50	333_A
10001	2	36.50	333_B
10002	2	717.52	BB_IM3
10002	4	1,746.40	BB_IM3
10003	1	32.62	A8899
10004	2	710.51	#N/A
10005	2	90.00	#N/A
10006	2	100.00	#N/A
	Total	46,141.22
Table 2 - Invoice Received From Vendor
PO Number	Quantity	Total Price	Stock Code
10001	2	696.10	AAA-01
10001	1	1,738.96	DDD-99
10001	2	108.28	CXC-790
10001	2	35.02	333_A
10001	2	35.02	333_B
10001	1	733.48	2147138
10001	2	40,090.04	2395815
10002	4	1746.4	BB_IM3
10002	2	717.52	BB_IM3
10003	1	32.62	A8899
	Total	45,933.44

 

[NBVC]

Try:

=IF(ISNUMBER(MATCH(A3,$G$3:$G$12,0)),INDEX($J$3:$J$12,SMALL(IF(MIN(ABS(IF(($G$3:$G$12=A3)*($H$3:$H$12=B3),$I$3:$I$12,10^10)-C3))=ABS(IF(($G$3:$G$12=A3)*($H$3:$H$12=B3),$I$3:$I$12,10^10)-C3),ROW($G$3:$G$12)-ROW($G$3)+1),COUNTIFS(A$3:A3,A3,B$3:B3,B3,C$3:C3,C3))),"N/A")

confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down.

Notice.. for the second item, it looks like the closest Stock Code would be 2147138 since Qty = 1, and price is closer to 733.48 (for 2147138) than to 1738.96 (for DDD-99) as you show in your expected results.

 

[Carol Su]

OMG.. It works!! haha.. thank you NBVC!!

One more concern here. In actual case, there will be many orders from many vendors for Table 1 & Table 2. And it is going to be 2 different excel file (different source) for each table. Which part of the formula shall I take note?
I was trying to study the formula and amend accordingly but failed. :confused2:Also, I am not sure about the one highlight in red as below.. shall I change the number "10" to say "500" if I have 500 records in Table 2?

=IF(ISNUMBER(MATCH(A3,$G$3:$G$12,0)),INDEX($J$3:$J$12,SMALL(IF(MIN(ABS(IF(($G$3:$G$12=A3)*($H$3:$H$12=B3),$I$3:$I$12,10^10)-C3))=ABS(IF(($G$3:$G$12=A3)*($H$3:$H$12=B3),$I$3:$I$12,10^10)-C3),ROW($G$3:$G$12)-ROW($G$3)+1),COUNTIFS(A$3:A3,A3,B$3:B3,B3,C$3:C3,C3))),"N/A")

Thanks for your time and advice in advance!

 

[NBVC]

You need to precede all the references to that second table with the path to that table.

if you have both workbooks open then you just select the references in the formula as they are (for example highlight $G$3:$G$12 in the formula, then go to the other sheet and select the new range you want to replace $G$3:$G$12 with).. then hit ENTER. You should have something like [Other Workbook.xls]Sheet1!$G$3:$G$500 (of course with your actual workbook, sheet and reference). Do this for each reference to the other sheet.

You do not have to replace the 10^10. That is a large number used in the formula to ensure that FALSE are replaced with that large number and therefore can't be part of the MIN differences.

 

[Carol Su]

Thanks NBVC! You just cured my headache!!:whoo: Merry Christmas and Happy New Year!