+ Reply to Thread
Results 1 to 2 of 2

Complicated If/Vloolup problem (may be VBA)

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    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
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    Adelaide, Australia
    MS-Off Ver

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

    Hi, you can do it with an array formula (see attached).


    Otherwise you will need a helper column in Item_PO if you just want to use Vlookups

+ 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. [SOLVED] VBA userform Textbox vloolup function
    By Hilton1982 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-03-2012, 10:32 AM
  2. complicated difference problem
    By GS751 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-18-2011, 03:45 AM
  3. Complicated Problem!
    By moonyboy99 in forum Excel General
    Replies: 2
    Last Post: 07-17-2008, 08:14 AM
  4. Column Count in vloolup
    By RANDY IN NC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2008, 02:59 PM
  5. Complicated array problem
    By semper_si in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-09-2007, 07:26 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


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