Pull unique items and their count from an unknown range size in excel

Question

I need to pull the unique names from column A on Sheet1 and on Sheet2 display only one of each name and the number of times it appeared. The names on Sheet 1 change daily, so I can't hard code any of them in.

Sheet1:
A
Joe    
Joe
Paul
Steve 
Steve
Steve

Sheet2:
A      B 
Joe    2
Paul   1
Steve  3

Code I have so far:

Sub testing()
    Dim data As Variant, temp As Variant
    Dim obj As Object
    Dim i As Long
    Set obj = CreateObject("scripting.dictionary")
    data = Selection
    For i = 1 To UBound(data)
        obj(data(i, 1) & "") = ""
    Next
    temp = obj.keys
    Selection.ClearContents
    Selection(1, 1).Resize(obj.count, 1) = Application.Transpose(temp)
End Sub

However, this is producing an error by itself.

It's giving me:

Joe 
Joe
Paul
Steve

I understand that this question is specifically asking for a vba answer, but a pivot table would do the job nicely, as well, since you can make the range be the entire column. — jlemley
– jlemley, Commented Jun 9, 2015 at 13:07

Gary's Student · Accepted Answer · 2015-06-09 13:01:45Z

2

Consider using .RemoveDuplicates:

Sub CountUniques()
   Dim r1 As Range, r2 As Range, r As Range
   Dim wf As WorksheetFunction

   Set wf = Application.WorksheetFunction
   Set r1 = Sheets("Sheet1").Columns(1).Cells
   Set r2 = Sheets("Sheet2").Range("A1")

   r1.Copy r2
   r2.EntireColumn.RemoveDuplicates Columns:=1, Header:=xlNo

   For Each r In r2.EntireColumn.Cells
      v = r.Value
      If v = "" Then Exit Sub
      r.Offset(0, 1).Value = wf.CountIf(r1, v)
   Next r
End Sub

answered Jun 9, 2015 at 13:01

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

Comments

Raystafarian · Accepted Answer · 2015-06-09 12:25:43Z

I wouldn't use a dictionary, personally, I'd do something like this -

Sub countem()
Dim origin As Worksheet
Set origin = Sheets("Sheet1")

Dim destination As Worksheet
Set destination = Sheets("Sheet2")

Dim x As Integer
x = origin.Cells(Rows.Count, "A").End(xlUp).Row

Dim y As Integer
y = 1
Dim strName As String
Dim rngSearch As Range

For i = 1 To x
 strName = origin.Cells(i, 1).Value
 Set rngSearch = destination.Range("A:A").Find(strName, , xlValues, xlWhole)
    If Not rngSearch Is Nothing Then
        rngSearch.Offset(, 1) = rngSearch.Offset(, 1) + 1
        Else: destination.Cells(y, 1) = strName
        destination.Cells(y, 2) = 1
        y = y + 1
    End If

 Next


End Sub

Just run through the origin searching for it on the destination, if found count++, otherwise add it.

user2579685 · Accepted Answer · 2015-06-09 13:38:42Z

A more verbose answer if you insisted on using the dictionary object and if perhaps you had more data processing to do.

' Create Reference to Microsoft Scripting Runtime
'   In VBE -> Tools -> References -> Microsoft Scripting Runtime
Option Explicit

Public Sub UniqueItems()
Dim rngInput As Range, rngOutput As Range
Dim vUniqueList As Variant

Set rngInput = ThisWorkbook.Worksheets(1).Range("A:A")
Set rngOutput = ThisWorkbook.Worksheets(2).Range("A:B")

vUniqueList = GetUniqueItems(rngInput)

rngOutput.ClearContents  
rngOutput.Resize(UBound(vUniqueList, 1), UBound(vUniqueList, 2)).Value =   vUniqueList

End Sub

Private Function GetUniqueItems(vList As Variant) As Variant
Dim sKey As String
Dim vItem As Variant
Dim oDict As Dictionary

    If IsObject(vList) Then vList = vList.Value

    Set oDict = New Dictionary

    For Each vItem In vList
        sKey = Trim$(vItem)
        If sKey = vbNullString Then Exit For
        AddToCountDict oDict, sKey
    Next vItem

    GetUniqueItems = GetDictData(oDict)

End Function

Private Sub AddToCountDict(oDict As Dictionary, sKey As String)
Dim iCount As Integer

  If oDict.Exists(sKey) Then
      iCount = CInt(oDict.Item(sKey))
      oDict.Remove (sKey)
  End If

  oDict.Add sKey, iCount + 1

End Sub

Private Function GetDictData(oDict As Dictionary) As Variant
Dim i As Integer
Dim vData As Variant

If oDict.Count > 0 Then
  ReDim vData(1 To oDict.Count, 1 To 2)
  For i = 1 To oDict.Count
      vData(i, 1) = oDict.Keys(i - 1)
      vData(i, 2) = oDict.Items(i - 1)
  Next i
  Else
  'return empty array on fail
  ReDim vData(1 To 1, 1 To 2)
End If

GetDictData = vData

End Function

Gary's Students solution definitely cleaner!

Collectives™ on Stack Overflow

Pull unique items and their count from an unknown range size in excel

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related