Results 1 to 8 of 8

Thread: Need help with excel formula!

  1. #1

    Need help with excel formula!



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Can you post a sample excel workbook, showing your setup and expected results, with explanation as required?


  3. #3

    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.
    Attached Files Attached Files

  4. #4
    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

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    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.


  6. #6
    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. 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!

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    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.


  8. #8
    Thanks NBVC! You just cured my headache!! Merry Christmas and Happy New Year!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •