# How to solve a issue with three criterias

#### most

##### Board Regular
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
Hi, I get a different result for row 6 than you expect - but maybe you can try:

#### most

##### Board Regular
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
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
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
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
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
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
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
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:

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

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