+ Reply to Thread
Results 1 to 3 of 3

Complicated If/Vloolup problem (may be VBA)

  1. #1
    Registered User
    Join Date
    03-01-2015
    Location
    Northants
    MS-Off Ver
    2007
    Posts
    15

    Complicated If/Vloolup problem (may be VBA)

    I am trying to work out the formula required to identify the Purchase Order number a customer will use, based on the Customer Number and, if a specific condition is met, the Item Number.


    The customers in question have provided P/O numbers in advance. The customers fall into one of two categories for Purchase Order classification: 'S' (the same P/O number for all items) or 'M' (P/O numbers are specific to the item number).


    I am trying to assign the P/O number to a customer/item combination (one per row), in a worksheet that contains all items ordered that day. This worksheet is then loaded to an ERP system.


    I have three worksheets:


    'CSVIn' (shows all of the order lines with the following relevant columns: 'Customer Code', 'Item Code', 'Quantity', 'Customer P/O No')


    'PO_Types' (a list of customer codes and the type of Purchase Order number system they use: 'S'='Same P/O number for all items' (shown here against the customer code);
    'M'='P/O number is item specific'). The columns here are 'Customer Code'; 'PO Type'; 'PO Number'. The 'PO Numbe'r will only be completed if the 'PO Type'='S'.


    'Item_PO' (a list of items that have specific P/O numbers - used where the PO_Type for a specific customer is 'S', ie the same number for all items)


    I cannot get my'head around the formula needed for this in worksheet 'CSVIn', Column 'CustPO' (where the customer's P/O for an item is stored ready for upload to the ERP system).


    The functionality, as I see it is:


    - In worksheet 'CSVIn', the formula in column D ('CusPO') needs to take the 'CustCode' in column A and look it up in column A ('Customer Code') of worksheet 'PO_Types'.
    - It will then examine the value of column C ('PO Type') of worksheet 'PO_Types' and take one of three actions
    1. If the customer code does not exist in worksheet 'PO_Types', it returns a null value (ie, "")
    2. If the customer exists and column C ('PO Type') of worksheet PO_Types contains 'S' (single P/O number for all items), it returns the value of column D ('PO_Number')
    3. If column C ('PO Type') of worksheet 'PO_Types' contains 'M' (P/O number depends on item number), it takes the values in column A ('Customer Code') and column B ('ItemCode') of worksheet 'CSVIn', and looks them up in worksheet 'Item_PO' (where the 'Customer Code' should appear in column A, and the 'Item Code' in column B), and returns the value in column C ('CurrentPO'). If there is no lookup match, it will return a null ("").

    Example of worksheet 'CSVIn'

    CustCode ItemCode Quantity CustPO
    C10092 141144 4
    C10092 0008 5
    C10092 91-208 5
    C10092 S2327-425-961 5
    C10058 3003015 2
    C10058 2604447002 2
    C10058 4304450 1
    C10058 02503556 1

    Example of worksheet 'PO_Types'

    Customer Code Customer Name PO Type PO Number
    C10058 CustA S PO_2016
    C10092 CustB M

    Example of worksheet 'Item_PO'

    Customer Code Item Code CurrentPO
    C10092 141144 5689
    C10092 0008 5689
    C10092 91-208 5689
    C10092 S2327-425-961 3985
    C10092 82-1074-002 3985

    Can anyone help with this, or am I over complicating it, or should it be done by VBA (in which case what would the code be?)?

    Many thanks

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,809

    Re: Complicated If/Vloolup problem (may be VBA)

    In cell D2 of CSVIn


    =IFERROR(IF(VLOOKUP(A2,PO_Types!A:C,3,FALSE)="S",VLOOKUP(A2,PO_Types!A:D,4,FALSE),IF(ISERROR(MATCH(B2,Item_PO!B:B,FALSE)),"Missing Item number",INDEX(Item_PO!C:C,SUMPRODUCT((A2=Item_PO!A:A)*(B2=Item_PO!B:B)*ROW(A:A))))),"Customer Number Missing")
    Last edited by Bernie Deitrick; 01-26-2016 at 02:43 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    03-01-2015
    Location
    Northants
    MS-Off Ver
    2007
    Posts
    15

    Re: Complicated If/Vloolup problem (may be VBA)

    Hi Bernie

    I have just tried this and it looks like it works brilliantly! I will test it further tomorrow.

    Many thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Complicated If/Vloolup problem (may be VBA)
    By tommagpieconsultants in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2016, 02:49 AM
  2. Complicated Solver Problem
    By kestefon in forum Excel General
    Replies: 3
    Last Post: 01-23-2015, 01:10 AM
  3. [SOLVED] VBA userform Textbox vloolup function
    By Hilton1982 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-03-2012, 10:32 AM
  4. Complicated Problem!
    By moonyboy99 in forum Excel General
    Replies: 2
    Last Post: 07-17-2008, 08:14 AM
  5. Column Count in vloolup
    By RANDY IN NC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2008, 02:59 PM
  6. [SOLVED] Quite a complicated problem
    By DaBeef in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2006, 11:55 AM
  7. Complicated IRR problem
    By JamesG in forum Excel General
    Replies: 2
    Last Post: 02-25-2006, 07:46 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1