Solved: UF Count Tally

[Emoncada]

I'm trying to have a userform display a Total count and I'm a little stumped on how I can make this happen.

I have a Sheet("Totals") with column B having a list of models.

What I would like is have it grab Range B1: LastRow and then comes the hard part.

I want it to tell me the model and how many there are.

So Example.
Column B

DC7100
DC7100
Dell 1920
DC7100
DC7200

Then I would like to show on a UserForm

DC7100 - 3
Dell 1920 - 1
DC7200 - 1

I am not sure if I need to get the total to be displayed on the "Totals" sheet first then pull that info into the UF, or If it can be done straight into the Userform.

Any ideas?

[shrivallabha]

Try this:
[VBA]Private Sub UserForm_Initialize()
Dim objDic As Object
Dim vList As Variant

Set objDic = CreateObject("Scripting.Dictionary")

With objDic
.CompareMode = TextCompare
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
If .Exists(Range("B" & i).Value) Then
.Item(Range("B" & i).Value) = .Item(Range("B" & i).Value) + 1
Else
.Add Range("B" & i).Value, 1
End If
Next i
vList = Application.Transpose(Array(.Keys, .Items))
End With

Set objDic = Nothing

With Me.ListBox1 'Change this listbox reference to suit
.ColumnCount = 2
.List = vList
End With

End Sub
[/VBA]

[Emoncada]

WWWWOOOOWWWW.
Shrivallabha, that is amazing.

Thank you so much.

Can this be done without the user being on "Totals" sheet?
Like have this run with the "Totals" sheet not being the active sheet?

[BrianMH]

[VBA]Private Sub UserForm_Initialize()
Dim objDic As Object
Dim vList As Variant
dim stTotals as worksheet
'change this to suit
set stTotals = thisworkbook.sheets("Totals")
Set objDic = CreateObject("Scripting.Dictionary")

With objDic
.CompareMode = TextCompare
For i = 1 To stTotals.Range("B" & Rows.Count).End(xlUp).Row
If .Exists(stTotals.Range("B" & i).Value) Then
.Item(stTotals.Range("B" & i).Value) = .Item(stTotals.Range("B" & i).Value) + 1
Else
.Add stTotals.Range("B" & i).Value, 1
End If
Next i
vList = Application.Transpose(Array(.Keys, .Items))
End With

Set objDic = Nothing

With Me.ListBox1 'Change this listbox reference to suit
.ColumnCount = 2
.List = vList
End With

End Sub
[/VBA]

This should work

[Emoncada]

Perfect. Thank you Shrivallabha, for you code.
Thank you BrianMH for adding the finishing touch.

Thanks Again.