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

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
335
Office Version
  1. 2010
Platform
  1. Windows
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
            .Add Cl.Value, 1
        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:

3.PNG


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):

4.PNG


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):

5.PNG


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
Joined
Jun 12, 2014
Messages
65,575
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Forum statistics

Threads
1,147,453
Messages
5,741,214
Members
423,649
Latest member
steel1968

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top