Need help with excel formula!

Carol Su

New member
Joined
Dec 18, 2013
Messages
6
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Can you post a sample excel workbook, showing your setup and expected results, with explanation as required?
 

Carol Su

New member
Joined
Dec 18, 2013
Messages
6
Reaction score
0
Points
0
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.
 

Attachments

  • Excel Guru - Excel Formula.xlsx
    12.8 KB · Views: 22

Carol Su

New member
Joined
Dec 18, 2013
Messages
6
Reaction score
0
Points
0
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 NumberQuantityTotal priceExpected Results
100011750.002147138
100011177.22DDD-99
100012710.51AAA-01
100012110.54CXC-790
10001240,922.902395815
10001236.50333_A
10001236.50333_B
100022717.52BB_IM3
1000241,746.40BB_IM3
10003132.62A8899
100042710.51#N/A
10005290.00#N/A
100062100.00#N/A
Total46,141.22
Table 2 - Invoice Received From Vendor
PO NumberQuantityTotal PriceStock Code
100012 696.10 AAA-01
100011 1,738.96 DDD-99
100012 108.28 CXC-790
100012 35.02 333_A
100012 35.02 333_B
100011 733.48 2147138
100012 40,090.04 2395815
1000241746.4 BB_IM3
100022717.52 BB_IM3
10003132.62 A8899
Total 45,933.44
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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

New member
Joined
Dec 18, 2013
Messages
6
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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

New member
Joined
Dec 18, 2013
Messages
6
Reaction score
0
Points
0
Thanks NBVC! You just cured my headache!!:whoo: Merry Christmas and Happy New Year!:D
 
Top