# Thread: How to solve a issue with three criterias Thanks:  4 Post #5352205 (1)Post #5344080 (1)Post #5344086 (1)Post #5346246 (1) Likes:  2 Post #5344080 (1)Post #5346246 (1)

1. ## 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. ## 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"))

3. ## Re: How to solve a issue with three criterias

Originally Posted by FormR
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. ## Re: How to solve a issue with three criterias

Try

Activity

 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

 Cell Formula 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

5. ## Re: How to solve a issue with three criterias

Originally Posted by most
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```

6. ## 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. ## 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. ## Re: How to solve a issue with three criterias

Originally Posted by 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.
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```

Originally Posted by 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...
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?

9. ## Re: How to solve a issue with three criterias

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. ## Re: How to solve a issue with three criterias

Originally Posted by most
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.