# Script that uses dictionary to get count of unique values in a multiple columns

#### kbishop94

##### Active Member
here is the code that I currently use (I got it here with some help ~2 years ago.)

It captures unique values in column C and then gets a count of those unique values.

It works great.

Code:
``````Dim Cl As Range
Dim Cnt, TmpA, TmpB
Dim i As Long, X As Long
' get a count of all unique values in column C (starting at row 1)
With CreateObject("scripting.dictionary")
For Each Cl In Worksheets("HELPER").Range("C1", Worksheets("HELPER").Range("C" & Rows.Count).End(xlUp))
If Not .Exists(Cl.Value) Then
Else
.Item(Cl.Value) = .Item(Cl.Value) + 1
End If
Next Cl
ReDim Cnt(0 To .Count - 1, 0 To 1)
For i = 0 To .Count - 1
Cnt(i, 0) = .keys()(i)
Cnt(i, 1) = .Items()(i)
Next i
End With
For i = LBound(Cnt, 1) To UBound(Cnt, 1) - 1
For X = i + 1 To UBound(Cnt, 1)
If Cnt(i, 1) < Cnt(X, 1) Then
TmpA = Cnt(X, 0)
TmpB = Cnt(X, 1)
Cnt(X, 0) = Cnt(i, 0)
Cnt(X, 1) = Cnt(i, 1)
Cnt(i, 0) = TmpA
Cnt(i, 1) = TmpB
End If
Next X
Next i
' take the data that was tallied from above and paste it onto the worksheet: "HELPER1" starting at A1
Worksheets("HELPER1").Range("A" & Rows.Count).End(xlUp).Offset(0).Resize(UBound(Cnt) + 1, 2).Value = Cnt``````

My question is, if I want to get a count/tally of unique values in other columns (the above code only addresses a single column... column 'C' in this case), can the above procedure be modified to capture not only column C, but also columns D and E?

Here is the original data that is located on the spreadsheet "HELPER" before the script is executed:

Here is what the above code that I posted returns (the code captures each unique value found in column 'C' and then provides a tally for each of those unique values and then posts that value in the adjacent cell):

But here is what I am would like it to do (do not only column C, but also column D and E in the same manner):

Can this be done (more easily or efficiently) with one procedure, or am I better off just having three separate procedures that capture and tally the three separate columns?

(I dont have a problem doing it that way, if I have to... but I am going to have alot of procedures in order to capture and tally all the required data that I am after.)

Thanks for any help or suggestions!

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### DanteAmor

##### Well-known Member
I wish I could buy you all a beer (or 2)!
Yes, I love that idea, haha

Again with pleasure. Thanks for the feedback

Replies
13
Views
374
Replies
13
Views
339
Replies
3
Views
213
Replies
3
Views
230
Replies
3
Views
126

1,147,451
Messages
5,741,191
Members
423,647
Latest member
lyanndominique

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back