VBA count the number of unique values

Question

I looked into the forum and I cannot really find the right answer to my problem. I have a list which looks like this:

ID ID2

SS_ID 1            T1              
SS_ID 1            T2
SS_ID 1            T2
SS_ID 1            T3
SS_ID 4            T1
SS_ID 4            T5

There is the 1st column which has IDs. The thing is that in the second column I have also ID numbers which are related with the 1st column.

So the list is in a sheet in Excel and I am trying to find with the help of VBA the number of Users from the 2nd column who are related to the 1st column. If by any chance there is a solution without VBA please feel free to help me. But as far as I can see it I only see the solution in VBA.

FYI I generate the list from a macro.I also found a way to remove the duplicates without deleting the rows which is necessarily in my case.

To Sum up, I need the sum of "unique" values from the 2nd column which are related to the 1st column. It is like a countif function but a little more complicated.

The result should look like this:

SS_ID 1    =    3
SS_ID 4    =    2

I am completely new to VBA.

What would be the output you want to get? I mean, You explained your problem and also you posted some data as example. What would be the correct answer with the data you posted? — Foxfire And Burns And Burns, Apr 25 '18 at 10:18
Hey I edited my post. i hope it helps to understand it a little bit more — David Morocutti, Apr 25 '18 at 10:23
Are you sure `SS_ID 1 = 2`? should not it be `SS_ID 1 = 3`? I ask these because `SS_ID 1` is related to `T1` and `T2` (twice) and `T3`. Am I right? — Foxfire And Burns And Burns, Apr 25 '18 at 10:25
@DavidMorocutti Would not it be `SS_ID 1 = 3` then? Since it has `T1`, `T2`, `T3`. — AntiDrondert, Apr 25 '18 at 10:25

score 3 · Answer 1 · answered Apr 25 '18 at 10:58

No need for vba unless you absolutely require it.

=SUMPRODUCT((A$2:INDEX(A:A, MATCH("Z", A:A))=D2)/(COUNTIFS(B$2:INDEX(B:B, MATCH("ZZZ", A:A)), B$2:INDEX(B:B, MATCH("ZZZ", A:A)),  A$2:INDEX(A:A, MATCH("Z", A:A)), D2)+(A$2:INDEX(A:A, MATCH("Z", A:A))<>D2)))

score 1 · Answer 2 · answered Apr 25 '18 at 10:46

Here is a macro-based solution:

Sub UniqueTable()
    Dim i As Long, N As Long, c As Collection, M As Long
    Dim j As Long, vd As String, va As String

    Columns("A:A").Copy Range("D1")

    ActiveSheet.Range("D:D").RemoveDuplicates Columns:=1, Header:=xlYes

    Range("E1").Value = "# of unique values"

    N = Cells(Rows.Count, "D").End(xlUp).Row
    M = Cells(Rows.Count, "B").End(xlUp).Row

    For i = 2 To N
        Set c = Nothing
        Set c = New Collection
        vd = Cells(i, "D").Value
        For j = 2 To M
            va = Cells(j, "A").Value
            If va = vd Then
                On Error Resume Next
                    c.Add Cells(j, "B").Value, CStr(Cells(j, "B").Value)
                On Error GoTo 0
            End If
        Next j
        Cells(i, "E").Value = c.Count
    Next i
End Sub

You can easily modify the code to place the little results table anywhere you like. If you add/remove/modify items in cols A and B, just rerun the code.

Thank you very much i will try to implement it into my macro :) — David Morocutti, Apr 25 '18 at 12:11

robotu · Answer 3 · 2018-04-25T10:34:42.680

0

Solution without VBA

You can use something like that: =SUMIFS(C1:C6;A1:A6;"=SS_ID 4")

The excel funtion "SOMMA.PIU.SE" in the attached image is "SUMIFS" in english. The SUMIFS function adds all of its arguments (A1:A6) that meet multiple criteria (A1:A6 => "=SS_ID 4").

edited Apr 25 '18 at 10:34

answered Apr 25 '18 at 10:32

robotu

203
2
7

VBA count the number of unique values

3 Answers3