Get unique count of column values in VBA based on filtering another column

Question

I have an excel sheet where there are 7 columns. I need to get unique count of users(in column A) based on filtering column C(Active - A, Inactive - I). Example

User_ID  Status
A1        A
A2        I
A1        A
A3        I
A2        I

Currently, I am using below code to get total count of the column but I need to first filter Status=A, then get unique count of column User_ID if any duplicates are there in column A. I searched on the internet but was not able to find any proper solution, I am very new to VBA so kindly help.

DSheet.Range("A2").End(xlDown).Row

Naveen Kumar · Accepted Answer · 2020-03-04 09:17:50Z

1

Use

Collection

Private Sub Test()
Dim Test As New Collection
Dim Test1 As New Collection
Dim rng As Range
For i = 2 To 6 'replace 6 with last row number
    Value = Cells(i, "A").Value
    check = Contains(Test, Value)
    check1 = Contains(Test1, Value)
    If Cells(i, "B").Value = "A" And Not check And Len(Value) > 0 Then
        MsgBox Value
        Test.Add Value, CStr(Value)
    ElseIf Cells(i, "B").Value = "I" And Not check1 And Len(Value) > 0 Then
        Test1.Add Value, CStr(Value)
    End If
Next i
On Error GoTo 0
MsgBox Test.count ' Distinct count for Status A
MsgBox Test1.count ' Distinct count for Status I
End Sub

'Function to check if the value exists in collection.
Public Function Contains(col As Collection, key As Variant) As Boolean
Dim obj As Variant
On Error GoTo err
    Contains = True
    obj = col(key)
    Exit Function
err:

    Contains = False
End Function

answered Mar 4, 2020 at 9:17

Naveen Kumar

1,9961 gold badge9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nipen Mahajan Over a year ago

can you pls explain in short the code written in For loop.

Naveen Kumar Over a year ago

I have used two collections. One for Status=A and one for Status=I. Then i am inserting values in both the collections based on the condition that it should not be there in respective collection by using Contains Function. This way items in both the collection will be unique.

Nipen Mahajan · Accepted Answer · 2020-03-04 13:50:42Z

0

Thank you so much for your help. I was able to execute through shorter code. I am filtering one column and getting unique counts based on filtered values. Sharing the code:

SSheet.Range("$A:$S").Autofilter field:=2, Criteria1:="A"
Set List=CreateObject(Scripting.Dictionary)

For Each ids In SSheet.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
 If Not List.Exists(ids.Value) Then List.Add ids.Value, Nothing
Next
MsgBox List.count

answered Mar 4, 2020 at 13:50

Nipen Mahajan

277 bronze badges

Collectives™ on Stack Overflow

Get unique count of column values in VBA based on filtering another column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related