Results 1 to 3 of 3

Array Function with Multiple, Variable, Dependent Criteria, Write Destination

Threaded View

kiosan Array Function with Multiple,... 07-15-2010, 01:42 PM
DonkeyOte Re: Array Function with... 07-15-2010, 02:43 PM
kiosan Re: Array Function with... 07-15-2010, 03:05 PM
  1. #1
    Registered User
    Join Date
    07-15-2010
    Location
    Somewhere Else
    MS-Off Ver
    Excel 2003
    Posts
    3

    Array Function with Multiple, Variable, Dependent Criteria, Write Destination

    I have a spreadsheet showing orders and manufacturing locations. Each order is broken down to the line level and the point of manufacture is shown for each line. I need to write a formula which will (a) check the "Order No" column for all occurences of each order number, then (b) find the first "POM Number" record associated with that order number, (c) determine if all POM instances for that Order match the first instance and (d) if so, write a "1" in column "Direct," else leave it blank (or write a "0" if something must be written).

    Direct POM Number Code Agent Order No (columns continue)
    1 040 129XY1 2000 90023A
    1 040 129XY1 2000 90023A
    _ 052 856G02 2000 90028A
    _ 040 129XY1 2000 90028A
    _ 052 856G02 2000 90028A


    The actual match is dependent on whether or not all POM cells for any given PO match each other. I don't want any of the records flagged if any single POM location for that order differs.

    The source file varies daily in the number of records, depending on how many orders are pending. (Today's file was about 7000 lines). The lines on an order will vary, depending on what we have in backlog. POM locations vary within a range of about 20 different locations. I'm trying to write a function that can simply be copied down as needed.

    I've tried nesting within a plain IF statement as follows: {=IF(SUM(($B$23:$B$5=B4)*($E$2:$E$23=C4)),1,0)}, but it returns a "1" every time - because, of course, cells always match themselves. I started to try a nested IF with a VLOOKUP, but that doesn't seem feasible. Since the data is dynamic, an INDEX (or other static location) call doesn't seem like it would work, either.

    Obviously, I'm missing something, but I don't know what.

    I am running Excel 2003 and have attached a small example file.

    I have searched but been unable to find anything similar dealing with dependent variables. Please point me in the right direction if I missed it. Any help at all would be greatly appreciated.
    Attached Files Attached Files
    Last edited by kiosan; 07-15-2010 at 03:05 PM.

Thread Information

Users Browsing this Thread

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

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