How to solve a issue with three criterias

most

Board Regular
Joined
Feb 22, 2011
Messages
77
Platform
Windows, Mobile
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

ABCDE
1PersonEmploymentStartDateEndDateExpected result
2194505022019-04-242019-11-01Active
3194505012013-06-172019-03-29Not active
4184810022016-01-012019-10-01Ignore
5184810022016-01-012019-10-01Ignore
6175007032019-07-232019-12-31Ignore
7175007022018-01-012019-06-30Ignore
8175007022018-01-012019-06-30Ignore
9166109052019-03-112019-12-31Active
10166109042019-01-012019-03-10Not active
11166109032018-09-102018-12-31Not active
12
13
14
15
16
172019-09-19

<tbody>
</tbody>
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,212
Office Version
365
Platform
Windows
Hi, I get a different result for row 6 than you expect - but maybe you can try:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Person</td><td style=";">Employment</td><td style=";">StartDate</td><td style=";">EndDate</td><td style=";">Expected result</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">194505</td><td style="text-align: right;;">2</td><td style="text-align: right;;">24/04/2019</td><td style="text-align: right;;">01/11/2019</td><td style="background-color: #FFFF00;;">Active</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">194505</td><td style="text-align: right;;">1</td><td style="text-align: right;;">17/06/2013</td><td style="text-align: right;;">29/03/2019</td><td style=";">Not Active</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">184810</td><td style="text-align: right;;">2</td><td style="text-align: right;;">01/01/2016</td><td style="text-align: right;;">01/10/2019</td><td style=";">Ignore</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">184810</td><td style="text-align: right;;">2</td><td style="text-align: right;;">01/01/2016</td><td style="text-align: right;;">01/10/2019</td><td style=";">Ignore</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">175007</td><td style="text-align: right;;">3</td><td style="text-align: right;;">23/07/2019</td><td style="text-align: right;;">31/12/2019</td><td style=";">Active</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">175007</td><td style="text-align: right;;">2</td><td style="text-align: right;;">01/01/2018</td><td style="text-align: right;;">30/06/2019</td><td style=";">Ignore</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">175007</td><td style="text-align: right;;">2</td><td style="text-align: right;;">01/01/2018</td><td style="text-align: right;;">30/06/2019</td><td style=";">Ignore</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">166109</td><td style="text-align: right;;">5</td><td style="text-align: right;;">11/03/2019</td><td style="text-align: right;;">31/12/2019</td><td style=";">Active</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">166109</td><td style="text-align: right;;">4</td><td style="text-align: right;;">01/01/2019</td><td style="text-align: right;;">10/03/2019</td><td style=";">Not Active</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">166109</td><td style="text-align: right;;">3</td><td style="text-align: right;;">10/09/2018</td><td style="text-align: right;;">31/12/2018</td><td style=";">Not Active</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">19/09/2019</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIFS(<font color="Red">$A$2:$A$11,A2,$B$2:$B$11,B2</font>)>1,"Ignore",IF(<font color="Red">AND(<font color="Green">C2<=$B$17,D2>=$B$17</font>),"Active","Not Active"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

most

Board Regular
Joined
Feb 22, 2011
Messages
77
Platform
Windows, Mobile
Hi, I get a different result for row 6 than you expect - but maybe you can try:
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"))

<tbody>
</tbody>

<tbody>
</tbody>
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,167
Office Version
365
Platform
Windows
Try

Excel Workbook
ABCDE
1PersonEmploymentStartDateEndDateExpected result
2194505224/04/20191/11/2019
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
1719/09/2019
Activity
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,167
Office Version
365
Platform
Windows
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
 

most

Board Regular
Joined
Feb 22, 2011
Messages
77
Platform
Windows, Mobile
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.
 

most

Board Regular
Joined
Feb 22, 2011
Messages
77
Platform
Windows, Mobile
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"))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,167
Office Version
365
Platform
Windows
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


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?
 

most

Board Regular
Joined
Feb 22, 2011
Messages
77
Platform
Windows, Mobile
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
PersonEmploymentStartDateEndDateExpected result
2
194505​
02
2019-04-24​
2019-11-01​
ActiveActive
2019-09-19​
3
194505​
01
2013-06-17​
2019-03-29​
Not ActiveNot active
4
184810​
02
2016-01-01​
2019-10-01​
IgnoreIgnore
5
184810​
02
2016-01-01​
2019-10-01​
IgnoreIgnore
6
175007​
03
2019-07-23​
2019-12-31​
IgnoreIgnore
7
175007​
02
2018-01-01​
2019-06-30​
IgnoreIgnore
8
175007​
02
2018-01-01​
2019-06-30​
IgnoreIgnore
9
166109​
05
2019-03-11​
2019-12-31​
ActiveActive
10
166109​
04
2019-01-01​
2019-03-10​
Not ActiveNot active
11
166109​
03
2018-09-10​
2018-12-31​
Not ActiveNot active
12
156108
02
2019-08-01
ActiveIgnore
13
156108
01
2018-12-01
2019-10-31
ActiveIgnore
14
256118
04
2019-05-06
2019-08-30
Not ActiveIgnore
15
256118
03
2018-08-20
2019-05-05
Not ActiveIgnore
Sheet: Sheet1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,167
Office Version
365
Platform
Windows
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:

Forum statistics

Threads
1,081,513
Messages
5,359,227
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top