For Each loop on Array to return count of each unique string

Question

What I am trying to achieve is to return a list of the unique values(Strings) in a column. Put that list into an array and then paste the list of values into a new sheet say column A. Then for each array element count how many times it occured the original list and return the count of its frequency in column B in the new sheet against its unique string.

This is my code so far.

Sub UniqueList()
Dim rListPaste As Range
Dim causeList As Range
Dim iReply As Integer
Dim element As Variant

On Error Resume Next

Set rListPaste = Application.InputBox _
(Prompt:="Please select the destination cell", Type:=8)

    If rListPaste Is Nothing Then
       iReply = MsgBox("No range nominated," _
          & " terminate", vbYesNo + vbQuestion)
      If iReply = vbYes Then Exit Sub
    End If


causeList = Range("E1", Range("E65536").End(xlUp))
Range("causeList").AdvancedFilter Action:=xlFilterCopy, Unique:=True
Range("causeList").AdvancedFilter CopyToRange:=causeList.Cells(1, 1)

element = 0
For Each element In causeList
    element = element + 1
Next element
End


End Sub

This might not the answer you are looking for, but still, its a way to do the same you want to do: Filter Unique values Count each value repetitions — Ricardo Ortega Magaña
– Ricardo Ortega Magaña, Commented Jan 14, 2013 at 10:56
The WorksheetFunction.Countif function will return the number of occurrences of a value within a range - use that rather than your last loop. Use a For Each Temprangevariable in UniqueRange on the resultant range from the CopyToRange - you can then set Temprangevariable.Offset(0,1) to the countif value. — MattCrum
– MattCrum, Commented Jan 14, 2013 at 11:03
Siddharth is spot on - just use a pivot and then instead of returning sums set the pivot to return counts. If you want to return a two column result where column A says Object = X and then column B says NumObjects = 10, with one row for each object then you should use a dictionary structure rather than an array — whytheq
– whytheq, Commented Jan 14, 2013 at 15:25
@SiddharthRout My main concern is automating it. I am in a corporate company and we have to continually download this sheet from sharepoint. The person who owns the infopath form that creates the spreadsheet has gone awol. The data in the spreadsheet is crappy and my soluton will need to be used by multiple sites. — sayth
– sayth, Commented Jan 14, 2013 at 23:23

Peter Albert · Accepted Answer · 2013-01-14 13:44:59Z

5

There are multiple ways to achieve what you're looking for:

1. Use PivotTable:

Just insert a PivotTable for you data range. Drop the field (the column name) you're interested in, in both - the row field and the data field. You'll see a list of unique items and the count next to it. If the data changes, you need to refresh the PivotTable

2. Create a list of unique values and add COUNTIF formula First, apply an Advanced Filter to your column (Data->Filter->Advanced). Here, select "Copy to another location", select your data range (as "List Range"), your destination ("Copy to") and check "Unique values" only. Now with that unique list, add a COUNTIF formula in the next column.

3. VBA

The following code will output the list of unique values and their frequency. You need to add a reference to the "Microsoft Scripting Library", as it uses the Dictionary object:

Sub CountUnique(rngInput As Range, rngTarget As Range)

    Dim d As New Dictionary
    Dim varCell As Variant
    Dim varKey As Variant
    Dim rngOut As Range
    For Each varCell In rngInput
        If Not d.Exists(varCell.Value) Then
            d.Add varCell.Value, 0&
        End If
        d(varCell.Value) = d(varCell.Value) + 1
    Next

    Set rngOut = rngTarget(1, 1)
    For Each varKey In d.Keys
        rngOut.Value = varKey
        rngOut.Offset(, 1) = d(varKey)
        Set rngOut = rngOut.Offset(1)
    Next

End Sub

answered Jan 14, 2013 at 13:44

Peter Albert

17.6k6 gold badges69 silver badges89 bronze badges

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

2 Comments

sayth Over a year ago

@Peter Albert thanks for the great options. I also could create a macro out of option 2 as well. I like your For Each loops they look good.

Peter Albert Over a year ago

Agree - you can also automate option 1 if need be. Probably the least effort in terms of lines of code - all you need is Sheets("SheetName").PivotTables(1).PivotCache.Refresh - assuming that you set up the pivottable with either a dynamic range name - or based on an Excel table/ListObject.

Collectives™ on Stack Overflow

For Each loop on Array to return count of each unique string

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related