VBA Count Unique Values in Column Based on Criteria from Another Column

[lamarh755]

I have a column ("AM") in Excel that lists LPN values. There can be duplicate LPNs in this column.
I have another column ("DR") that contains numeric values.
I would like to count the number of LPNs in Range("AM8:AM" & lastRow) in which the values in Range("DR8:DR" & lastRow) are ">=3.00"
I would like to count each LPN only once, so no duplicate values in Range("AM8:AM & lastRow)
In this case, lastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

I would like the count result to be displayed in Range("N5")

Any assistance would be greatly appreciated.

 

[Eric W]

Try:

Sub test1()
Dim dic As Object, MyData As Variant, i As Long

    Set dic = CreateObject("Scripting.Dictionary")
    MyData = Range("AM8:DR" & Cells(Rows.Count, "AM").End(xlUp).Row)
    
    For i = 1 To UBound(MyData)
        If MyData(i, UBound(MyData, 2)) >= 3 Then dic(MyData(i, 1)) = 1
    Next i
    
    Range("N5").Value = dic.Count
End Sub

 

[lamarh755]

Try:

Sub test1()
Dim dic As Object, MyData As Variant, i As Long

    Set dic = CreateObject("Scripting.Dictionary")
    MyData = Range("AM8:DR" & Cells(Rows.Count, "AM").End(xlUp).Row)
   
    For i = 1 To UBound(MyData)
        If MyData(i, UBound(MyData, 2)) >= 3 Then dic(MyData(i, 1)) = 1
    Next i
   
    Range("N5").Value = dic.Count
End Sub

This worked! Thank you!!!

 

[Eric W]

Happy to help!

 

[Peter_SSs]

This worked! Thank you!!!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.