Excel VBA Generating report based on grouping

Question

I have an Excel VBA that I created. It generates an output based on an input file.

In that file, there is a field that contains usernames, these usernames are repeated throughout the column.

What I need to do is, assign the usernames to separate cells to be unique not repeated, and then count the repeated ones.

Answer 1

Suppose I have my excel worksheet setup as follows:

1. List of usernames in column A
2. In column B I want all unique usernames

3. In column C I want a count of how many of each name there are

       Username     Unique     Count
   1   John         John       1
   2   Susan        Susan      2
   3   Chris        Chris      3
   4   Susan
   5   Chris
   6   Chris
   

I can do this in VBA by putting the usernames into a dictionary where the Key is the username and the Value is the number of repetitions. I then just iterate over the dictionary to get the desired output.

This code worked for me:

Sub UserNameCount()
    Dim usernames As Range, name As Range, rw As Long

    Set dict = CreateObject("Scripting.Dictionary")
    Set usernames = Range("A2:A" & Range("A1").End(xlDown).Row)
    rw = 2

    For Each user In usernames
        If Not dict.Exists(user.Value) Then
            dict.Add user.Value, 1
        Else
            dict.Item(user.Value) = dict.Item(user.Value) + 1
        End If
    Next user

    For Each v In dict.Keys
        Range("B" & rw) = v
        Range("C" & rw) = dict.Item(v)
        rw = rw + 1
    Next

    Set dict = Nothing
End Sub

NB: you may need to do Tools > References > Microsoft Scripting Runtime to get the dictionary to work