Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: How to solve a issue with three criterias

  1. #1
    Board Regular
    Join Date
    Feb 2011
    Location
    Sweden
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to solve a issue with three criterias

    Hi,
    I would like some input/pointers on how to solve this, I'm not sure where to begin...
    I want to write Active, Not active or Ignore in column E based on three criteria.

    As you can see in the table, a person can occur 2-3 times, even 6 times in my orginal data. Data is sorted on column A.

    If same employment (column B) occurs two times = all rows should be Ignore.
    If employment (column B) differs then check if startdate (column C) and enddate (column D) is within range of "orange date" (B17) then Active else Not active.

    /Marcus

    A B C D E
    1 Person Employment StartDate EndDate Expected result
    2 194505 02 2019-04-24 2019-11-01 Active
    3 194505 01 2013-06-17 2019-03-29 Not active
    4 184810 02 2016-01-01 2019-10-01 Ignore
    5 184810 02 2016-01-01 2019-10-01 Ignore
    6 175007 03 2019-07-23 2019-12-31 Ignore
    7 175007 02 2018-01-01 2019-06-30 Ignore
    8 175007 02 2018-01-01 2019-06-30 Ignore
    9 166109 05 2019-03-11 2019-12-31 Active
    10 166109 04 2019-01-01 2019-03-10 Not active
    11 166109 03 2018-09-10 2018-12-31 Not active
    12
    13
    14
    15
    16
    17 2019-09-19

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How to solve a issue with three criterias

    Hi, I get a different result for row 6 than you expect - but maybe you can try:

    Excel 2013/2016
    ABCDE
    1PersonEmploymentStartDateEndDateExpected result
    2194505224/04/201901/11/2019Active
    3194505117/06/201329/03/2019Not Active
    4184810201/01/201601/10/2019Ignore
    5184810201/01/201601/10/2019Ignore
    6175007323/07/201931/12/2019Active
    7175007201/01/201830/06/2019Ignore
    8175007201/01/201830/06/2019Ignore
    9166109511/03/201931/12/2019Active
    10166109401/01/201910/03/2019Not Active
    11166109310/09/201831/12/2018Not Active
    12
    13
    14
    15
    16
    1719/09/2019

    Sheet1



    Worksheet Formulas
    CellFormula
    E2=IF(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2)>1,"Ignore",IF(AND(C2<=$B$17,D2>=$B$17),"Active","Not Active"))

    [code]your code[/code]

  3. #3
    Board Regular
    Join Date
    Feb 2011
    Location
    Sweden
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to solve a issue with three criterias

    Quote Originally Posted by FormR View Post
    Hi, I get a different result for row 6 than you expect - but maybe you can try:
    Worksheet Formulas
    Cell Formula
    E2 =IF(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2)>1,"Ignore",IF(AND(C2<=$B$17,D2>=$B$17),"Active","Not Active"))
    Forget to mention, I prefer a VBA solution, but I got the same result as you and Business might actually accept this.
    Thanks a lot!
    If someone have some other idea, please share.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How to solve a issue with three criterias

    Try

    Activity

    ABCDE
    1PersonEmploymentStartDateEndDateExpected result
    2194505224/04/20191/11/2019Active
    3194505117/06/201329/03/2019Not Active
    418481021/01/20161/10/2019Ignore
    518481021/01/20161/10/2019Ignore
    6175007323/07/201931/12/2019Ignore
    717500721/01/201830/06/2019Ignore
    817500721/01/201830/06/2019Ignore
    9166109511/03/201931/12/2019Active
    1016610941/01/201910/03/2019Not Active
    11166109310/09/201831/12/2018Not Active
    12
    13
    14
    15
    16
    17 19/09/2019

    Spreadsheet Formulas
    CellFormula
    E2=IF(AGGREGATE(14,6,COUNTIFS($A$2:$A$11,A2,B$2:B$11,B$2:B$11),1)>1,"Ignore",IF(MEDIAN(C2,D2,B$17)=B$17,"Active","Not Active"))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How to solve a issue with three criterias

    Quote Originally Posted by most View Post
    Forget to mention, I prefer a VBA solution, ..
    The vba could basically use the formula I suggested.
    I have assumed that the "orange date" cell is a named range "FixedDate"

    Code:
    Sub Activity()
      Dim lr As Long
      
      lr = Range("D" & Rows.Count).End(xlUp).Row
      With Range("E2:E" & lr)
        .Formula = Replace(Replace("=IF(AGGREGATE(14,6,COUNTIFS($A$2:$A$#,A2,B$2:B$#,B$2:B$#),1)>1,""Ignore"",IF(MEDIAN(C2,D2,^)=^,""Active"",""Not Active""))", "#", lr), "^", Range("FixedDate").Address)
        .Value = .Value
      End With
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular
    Join Date
    Feb 2011
    Location
    Sweden
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to solve a issue with three criterias

    Your "=IF(AGGREGATE(14,6,..." formula works flawless. Thanks!
    But I identified a new example, EndDate can be blank were expected result should be Active.
    My solution to that was to replace D2 with (IF(D2="";"2100-01-01";D2))

    As you know, your VBA solution is only inserting the formula. I meant a more pure VBA solution which fills a text value in column E based on the criteria.
    My original data has about 1000 lines, and with 8 threads this takes about 1-2 minutes to update, not a biggie but makes it kind of inconvenient for each update.

  7. #7
    Board Regular
    Join Date
    Feb 2011
    Location
    Sweden
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to solve a issue with three criterias

    I have now also found out that two rows can be ACTIVE, so I need to identify them some how.

    I'm trying to solve this with formulas Match and Countifs, but I can't get it to work. I don't really understand how to use them...
    Code:
    =IF(MATCH(A2;A$2:A$1197;0);COUNTIFS(E$2:E$1197;"Active"))

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How to solve a issue with three criterias

    Quote Originally Posted by most View Post
    Your "=IF(AGGREGATE(14,6,..." formula works flawless. Thanks!
    But I identified a new example, EndDate can be blank were expected result should be Active.
    My solution to that was to replace D2 with (IF(D2="";"2100-01-01";D2))

    As you know, your VBA solution is only inserting the formula. I meant a more pure VBA solution which fills a text value in column E based on the criteria.
    My original data has about 1000 lines, and with 8 threads this takes about 1-2 minutes to update, not a biggie but makes it kind of inconvenient for each update.
    Try this version
    Code:
    Sub Activity_v2()
      Dim d1 As Object, d2 As Object
      Dim a As Variant
      Dim i As Long
      Dim OrangeDate As Date
      
      Set d1 = CreateObject("Scripting.Dictionary")
      Set d2 = CreateObject("Scripting.Dictionary")
      a = Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row).Value
      OrangeDate = Range("FixedDate").Value
      For i = 1 To UBound(a)
        If d1.exists(a(i, 1) & "|" & a(i, 2)) Then
          d2(a(i, 1)) = Empty
        Else
          d1(a(i, 1) & "|" & a(i, 2)) = Empty
        End If
      Next i
      For i = 1 To UBound(a)
        If d2.exists(a(i, 1)) Then
          a(i, 5) = "Ignore"
        Else
          If IsEmpty(a(i, 4)) Then
            If OrangeDate >= a(i, 3) Then
              a(i, 5) = "Active"
            Else
              a(i, 5) = "Not Active"
            End If
          ElseIf OrangeDate >= a(i, 3) And OrangeDate <= a(i, 4) Then
            a(i, 5) = "Active"
          Else
            a(i, 5) = "Not Active"
          End If
        End If
      Next i
      Range("E2").Resize(UBound(a)).Value = Application.Index(a, 0, 5)
    End Sub


    Quote Originally Posted by most View Post
    I have now also found out that two rows can be ACTIVE, so I need to identify them some how.

    I'm trying to solve this with formulas Match and Countifs, but I can't get it to work. I don't really understand how to use them...
    Code:
    =IF(MATCH(A2;A$2:A$1197;0);COUNTIFS(E$2:E$1197;"Active"))
    I don't understand exactly what you want here. Could we have a small set of sample data with result(s) & any further clarification that you can give?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    Board Regular
    Join Date
    Feb 2011
    Location
    Sweden
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to solve a issue with three criterias

    Thanks, your scripts works flawless.
    I've added two new use case, line 12-15. The script generates Active or Not active on both, but expected is Ignore on all four.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Person Employment StartDate EndDate Expected result
    2
    194505
    02
    2019-04-24
    2019-11-01
    Active Active
    2019-09-19
    3
    194505
    01
    2013-06-17
    2019-03-29
    Not Active Not active
    4
    184810
    02
    2016-01-01
    2019-10-01
    Ignore Ignore
    5
    184810
    02
    2016-01-01
    2019-10-01
    Ignore Ignore
    6
    175007
    03
    2019-07-23
    2019-12-31
    Ignore Ignore
    7
    175007
    02
    2018-01-01
    2019-06-30
    Ignore Ignore
    8
    175007
    02
    2018-01-01
    2019-06-30
    Ignore Ignore
    9
    166109
    05
    2019-03-11
    2019-12-31
    Active Active
    10
    166109
    04
    2019-01-01
    2019-03-10
    Not Active Not active
    11
    166109
    03
    2018-09-10
    2018-12-31
    Not Active Not active
    12
    156108
    02
    2019-08-01
    Active Ignore
    13
    156108
    01
    2018-12-01
    2019-10-31
    Active Ignore
    14
    256118
    04
    2019-05-06
    2019-08-30
    Not Active Ignore
    15
    256118
    03
    2018-08-20
    2019-05-05
    Not Active Ignore
    Sheet: Sheet1

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How to solve a issue with three criterias

    Quote Originally Posted by most View Post
    I've added two new use case, line 12-15. The script generates Active or Not active on both, but expected is Ignore on all four.
    You have given no logic as to why they should be Ignore.

    Row 12
    In post 6 you said "EndDate can be blank were expected result should be Active."

    Row 13
    Post 1 "If employment (column B) differs then check if startdate (column C) and enddate (column D) is within range of "orange date" (B17) then Active"

    Rows 14 & 15
    Post 1 "If employment (column B) differs then check if startdate (column C) and enddate (column D) is within range of "orange date" (B17) then ... else Not active."

    The code results seem to agree with all of those rules.

    Further, the condition for Ignore was "If same employment (column B) occurs two times = all rows should be Ignore.". None of rows 12:15 meet that condition.
    Last edited by Peter_SSs; Oct 5th, 2019 at 10:41 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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