I have a task in Excel VBA, which I have never done before and I'm thinking if there is a simpler solution than mine.

I have a table in a sheet that looks like this:

And I want to get a number of unique names, that have 4 or more points. So I want to get a table that looks like this:

In my real data I don't know how many unique names is in a set.

I thought of using Collection to solve this. I used it when I want to count number of unique names and it work fine, but now I want to get a sum of values in different columns. I will have a collection of sum of points as an Item and Name as a Key:

last_row = Range("A1").End(xlDown).Row
On Error Resume Next
For i = 3 To last_row
    If coll.Contains(.Cells(i, 1)) Then
       a = coll.Item(CStr(.Cells(i, 1)))
       a = a   .Cells(i, 2)
       coll.Remove (CStr(.Cells(i, 1)))
       coll.Add a, CStr(Cells(i, 1))
    Else 
       coll.Add .Cells(i, 1), CStr(.Cells(i, 2))
    End If
Next

And then go through the collection and count how many names is in a specific group.

I think that is the only way to do this using collection, because I can't just add a number to the collection item, but maybe there is a some simpler way to to this?

Thanks a lot for every help!

CodePudding user response：

You can make a solution, based on the =UNIQUE(), =SUMIF() and =COUNTIF() functions, as explained here (I've put your data in cells A1:B6).

Cell D2 contains =UNIQUE(A2:A6) and automatically fills cells D2, D3 and D4.
Cell E2 contains =SUMIF($A$2:$A$6,D2,$B$2:$B$6), E3 and E4 are dragged down.

Cell H2 contains =COUNTIF(E2:E4,"<4").
Cell H3 contains =COUNTIF(E2:E4,">=4").

Hereby a screenshot:

CodePudding user response：

For good measure, an answer you have not asked for but I wanted to give anyway in case you have access to the newest worksheet-functions:

=VSTACK({"Group","Number"},LET(X,4,Y,SUMIF(A2:A6,UNIQUE(A2:A6),B2:B6),HSTACK({"<";">="}&X,CHOOSE({1;2},SUM(--(Y<X)),SUM(--(Y>=X))))))

Where 'X' is the boundary you want to calculate. Consequently, if I had to change the 'X'-variable to hold '5', the output will look like: