Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA to count and print distinct values

Tags:

excel

vba

I have to count number of distinct values from a column and print it with the distinct value and count in another sheet. I am working with this piece of code, but for some reason, it is not returning any result. Could anyone tell me where I am missing the piece!

Dim rngData As Range
Dim rngCell As Range
Dim colWords As Collection
Dim vntWord As Variant
Dim Sh As Worksheet
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Sh3 As Worksheet

On Error Resume Next

Set Sh1 = Worksheets("A")
Set Sh2 = Worksheets("B")
Set Sh3 = Worksheets("C")

Sh1.Range("A2:B650000").Delete

Set Sh = Worksheets("A")
Set r = Sh.AutoFilter.Range
r.AutoFilter Field:=24
r.AutoFilter Field:=24, Criteria1:="My Criteria"

Sh1.Range("A2:B650000").Delete

Set colWords = New Collection

Dim lRow1 As Long
lRow1 = <some number>

Set rngData = <desired range>
For Each rngCell In rngData.Cells
    colWords.Add colWords.Count + 1, rngCell.Value
    With Sh1.Cells(1 + colWords(rngCell.Value), 1)
        .Value = rngCell.Value
        .Offset(0, 1) = .Offset(0, 1) + 1
    End With
Next

Above is my full code.. My required outcome is simple, count number of occurrences of each cell in a column, and print it in another sheet with the count of occurrences. Thanks!

Thanks! Navs.

like image 746
user1087661 Avatar asked Mar 12 '12 07:03

user1087661


People also ask

How do I count unique values in Excel VBA?

We can use the COUNTIF function of Excel in VBA to count the unique values in a column that appears only once. The COUNTIF function returns the number of times a value appears in a column. So we can use it to find out the number of times a value appears in a column. And if it's 1, we'll include it in our counting.

How do I count distinct and unique values in Excel?

You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears.

Is there a count distinct function in Excel?

To count distinct values in excel, first enter the formula =SUM(1/COUNTIF(range, range)) in the desired cell. The range specifies the starting cell and ending cell separated by a colon. This is an array function, so press Ctrl+Shift+Enter to apply the formula.

How do I count values in VBA?

Step 1: Go to Insert menu tab and click on Module option as shown below from the list. Step 2: Select the range of cell where we want to see the output. Here that cell is C12. Step 3: Now use the count function in inverted commas in select the range of those cells which we need to count.


1 Answers

This is extreamlly easy and practical to do using a dictionary object. The logic is similar to Kittoes answer, but the dictionary object is much faster, effecient, and you can output an array of all keys and items, which you want to do here. I have simplified the code to generating a list from column A, but you will get the idea.

Sub UniqueReport()

Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim varray As Variant, element As Variant

varray = Range("A1:A10").Value

'Generate unique list and count
For Each element In varray
    If dict.exists(element) Then
        dict.Item(element) = dict.Item(element) + 1
    Else
        dict.Add element, 1
    End If
Next

'Paste report somewhere
Sheet2.Range("A1").Resize(dict.Count, 1).Value = _
    WorksheetFunction.Transpose(dict.keys)
Sheet2.Range("B1").Resize(dict.Count, 1).Value = _
    WorksheetFunction.Transpose(dict.items)

End Sub

How it works: You just dump the range into a variant array to loop through quickly, then add each to the dictionary. If it exists, you just take the item that goes with they key (starts at 1) and add one to it. Then at the end just slap the unique list and the counts wherever you need them. Please note that the way I create an object for the dictionary allows anyone to use it - there is no need to add a reference to your code.

like image 68
aevanko Avatar answered Oct 04 '22 09:10

aevanko