1

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.

Improve this question
2
  • 13
    Use a Pivot table. You don't need vba for this :) Commented Feb 15, 2013 at 13:51
  • 1
    What have you tried so far? You could use pivot tables or worksheet functions like COUNTIF. Commented Feb 15, 2013 at 13:54

3 Answers 3

Reset to default
10

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks! Pictures make things so much easier.
thank you,i will use pvote table option , i can able to get my out now
If this or any other answer helped solve your question, please mark it as solved. It helps others know there was a solution to your problem.
3

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")
Improve this answer

1 Comment

thank you,i will use pvote table option , i can able to get my out now
2

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.

Improve this answer

2 Comments

I don't think it's easier than a pivot table if you want to capture a changing list of customers.
@DougGlancy - I agree with you. I am giving this as an alternative "if you only want to know the number of times certain customers appear". The other thing I don't like about pivot tables is that they can change size, and need recalculating. I thought the OP would benefit from seeing the alternatives. I am not saying this is "better", just "different" - and I think the 9 "helpful" flags that Siddarth Rout got should be enough to get OP's attention to the concept of pivot tables...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.