+ Reply to Thread
Results 1 to 4 of 4

If Statement Help

  1. 01-27-2016, 03:59 PM #1
    johnsor1
    johnsor1 is offline
    Registered User
    Join Date
    02-17-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    62

    Question If Statement Help

    Hi:

    I am unable to upload an excel file.... lets consider the below in a spreadsheet.

    I am seeking help for my current issue. If the following applies then flag column D.

    If the "Work Ctr" is RNDCL has greater than 4 orders in the same week then flag in column D with a Yes. Less than 4 per week then "NO"

    There cannot be more than 1 order number in a given week.

    Many of these have duplicates and need to be excluded. I have a mass amount of data like this so I can't simply remove the duplicates but need it to automatically include no more than 1 specific order per week.

    Columns should be

    A) Order Number
    B) Work Ctr
    C) Finish Week (Date)
    D) New Column


    Order Number Work ctr Finish Week (Date)[/B] New Column(D)
    3005687115-40 RNDCL 1/29/2016
    3005687115-20 RNDCL 2/5/2016
    3005687115-20 RNDCL 2/5/2016
    3005687115-20 RNDCL 2/5/2016
    3005687115-30 RNDCL 1/29/2016
    3005687115-30 RNDCL 1/29/2016
    3005687115-30 RNDCL 1/29/2016
    3005687115-40 RNDCL 1/29/2016
    3005687115-40 RNDCL 1/29/2016
    3005687115-40 RNDCL 1/29/2016
    3005744429-10 RNDCL 1/29/2016
    3005744429-20 RNDCL 1/29/2016
    3005744429-10 RNDCL 2/12/2016
    3005744429-10 RNDCL 2/12/2016
    3005744429-10 RNDCL 2/12/2016
    3005744429-20 RNDCL 2/12/2016
    3005744429-20 RNDCL 2/12/2016
    3005744429-20 RNDCL 2/12/2016
    3005753341-10 RNDCL 2/26/2016
    3005753341-20 RNDCL 3/4/2016
    3005729844-10 RNDCL 2/26/2016
    3005747247-20 RNDCL 3/11/2016
    3005753341-10 RNDCL 3/4/2016
    3005753341-10 RNDCL 3/4/2016
    3005753341-10 RNDCL 3/4/2016
    3005753341-20 RNDCL 3/4/2016
    3005753341-20 RNDCL 3/4/2016
    3005753341-20 RNDCL 3/4/2016
    3005729844-10 RNDCL 3/11/2016
    3005729844-10 RNDCL 3/11/2016
    3005729844-10 RNDCL 3/11/2016
    3005747247-20 RNDCL 3/11/2016
    3005747247-20 RNDCL 3/11/2016
    3005747247-20 RNDCL 3/11/2016
    3005768234-10 RNDCL 3/18/2016
    3005768234-10 RNDCL 3/31/2016
    3005768234-10 RNDCL 3/31/2016
    3005768234-10 RNDCL 3/31/2016
    Reply With QuoteRegister To Reply
  2. 01-27-2016, 04:38 PM #2
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: If Statement Help

    I would insert a column between columns C and D and enter this formula and fill down to extract the week number from the date
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    In column E (formerly D) enter this formula and fill down to count the orders for each week.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    A Pivot Table would summarize the weeks.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  3. 01-27-2016, 05:14 PM #3
    johnsor1
    johnsor1 is offline
    Registered User
    Join Date
    02-17-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: If Statement Help

    Thank you the response.

    One additional formula (If Possible). Without actually deleting the duplicates in Column A. I need each order number to count no more than just "one" time in the formula.

    For example the week below.. has 6 orders for the week but would only count for 2 since 4 of the 6 are duplicates.

    From this point the 2 would be part of the formula.. to account for the "Yes"= Greater than 4. No= 4 or less. This would be a NO for what I am looking for.

    3005687115-30 RNDCL 1/29/2016 5 Yes
    3005687115-30 RNDCL 1/29/2016 5 Yes
    3005687115-30 RNDCL 1/29/2016 5 Yes
    3005687115-40 RNDCL 1/29/2016 5 Yes
    3005687115-40 RNDCL 1/29/2016 5 Yes
    3005687115-40 RNDCL 1/29/2016 5 Yes
    Reply With QuoteRegister To Reply
  4. 01-27-2016, 06:02 PM #4
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: If Statement Help

    I am getting a little lost with what counts. You only want a YES if the week has 4 or more UNIQUE ORDERS...correct? If that is the case, then, in your example, I see that only week 5 has 4 or more unique orders and the rest have less.
    I added another helper column to give a 1 for the first order and "" for all other orders of the same number.
    Enter in E2 and fill down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    The formula in F2 filled down would then become
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This gets to be confusing because some of the orders are split between weeks. Where this happens, the first appearance of the order is what is counted.
    Attached Files Attached Files
    Last edited by newdoverman; 01-27-2016 at 06:04 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. nested IF statement or AND? trying to write an IF statement with multiple conditions
    By kcphila in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2015, 04:25 PM
  2. [SOLVED] If statement to select data - nested statement - assistance
    By petitesouris in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-29-2015, 09:55 PM
  3. compile error expected line number statement end statement
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2014, 10:12 AM
  4. VBA Compile Error : line number or label or statement or end of statement
    By excellearner121 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2013, 06:41 PM
  5. [SOLVED] Convert If statement with cell row reference calculations to vlookup statement
    By excyclist in forum Excel General
    Replies: 4
    Last Post: 06-01-2012, 10:05 AM
  6. VBA- Compile Error, Expected: line number or label or statement or end of statement
    By RLK7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2012, 05:33 PM
  7. [SOLVED] Utilize a Select Case Statement in Target Intersect Statement
    By max57 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2009, 08:55 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1