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?

## Like this thread? Share it with others