CODEWITHSUNDEEP
vbaexcel
user2075655
user2075655

Reputation: 11

How to count repeated values using VBA

I am new to VBA and I am trying to find the number of times the same values are repeated in particular column.

Then I need to paste the value and the count of the repeated value in column A and B of another sheet.

I need to count the number of times repeated values:

PSV_Cust_1
PSV_Cust_1
PSV_Cust_1
PSV_Cust_1
PSV_Cust_1
PSV_Cust_2
PSV_Cust_2
PSV_Cust_2
PSV_Cust_2
PSV_Cust_3
PSV_Cust_3
PSV_Cust_3
PSV_Cust_3
PSV_Cust_4
PSV_Cust_4
PSV_Cust_4
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5

Result should be:

Value        Count
PSV_Cust_1   5
PSV_Cust_2   4
PSV_Cust_3   4
PSV_Cust_4   3
PSV_Cust_5   7

Please any one help to get the outpu using VBA code.

Upvotes: 1

Views: 24121

Answers (3)

StoriKnow
StoriKnow

Reputation: 5866

As others have suggested, a Pivot Table would be the simplest way to accomplish this. Here's how:

1) Select the rows you want to count

2) Choose Insert -> PivotTable from the ribbon

3) A window will appear, click Ok to create your pivot table:

enter image description here

4) On the right under "PivotTable Field List: Choose fields to add to report:" click the checkbox next:

enter image description here

5) Now drag the checkbox field you just clicked down to the "Values" list and let go:

enter image description here

6) That's it! You will now have what you asked for:

enter image description here

Upvotes: 10

Floris
Floris

Reputation: 46365

The COUNTIF(range, value) function will do what you want - it has the advantage that you can more easily constrain the values you want to search for (compared to a pivot table). Imagine your data is in range "Sheet1!A1:A25" which you have named "customers" (you do this by selecting the range and typing customers in the address box to the left of the formula bar).and contains "customer 1, customer 2... through customer 10", and you only want to know how many times customers 1 and 5 appear, you can create the following on sheet2

col A        col B
Value        count
customer 1   =COUNTIF(customers, A1)
customer 5   =COUNTIF(customers, A2)

Of course you can just drag the formula down from cell B1 - you don't need to type it in again.

This will count the customers, update automatically, ... I think it's easier than a pivot table in this example.

Upvotes: 2

Peter Albert
Peter Albert

Reputation: 17475

This macro will do what you need:

Sub Summarize(rngSource As Range, rngTarget As Range)
    Dim d As New Scripting.Dictionary
    Dim rng As Range
    Dim var As Variant

    For Each rng In rngSource
        If rng <> "" Then
            If d.Exists(rng.Value) Then
                d(rng.Value) = d(rng.Value) + 1
            Else
                d.Add rng.Value, 1
            End If
        End If
    Next rng

    rngTarget = "Value"
    rngTarget.Offset(, 1) = "Count"
    Set rng = rngTarget.Offset(1)
    For Each var In d.Keys
        rng = var
        rng.Offset(, 1) = d(var)
        Set rng = rng.Offset(1)
    Next
End Sub

You need to add a reference to the Microsoft Scripting Library in the Visual Basic Editor (Tools->References). You can call it like this:

Summarize Sheet1.Range("A1:A24"), Sheet1.Range("C1")

Upvotes: 3

Related Questions