How to solve a issue with three criterias

[most]

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

<tbody>
</tbody>

 

[FormR]

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

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

Cell Formulas
Range	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"))

 

[most]

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")) <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]

Try

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

 

[Peter_SSs]

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"

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]

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]

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

=IF(MATCH(A2;A$2:A$1197;0);COUNTIFS(E$2:E$1197;"Active"))

 

[Peter_SSs]

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

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

=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]

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

 

[Peter_SSs]

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.