How to count repeating values in an array (VBA)?

Question

Assume I have an array that has the following data:

apples
apples
apples
apples
oranges
grapes
oranges
apples
oranges
grapes
bananas

How can I make the output look like this:

Item        count
apples        5
oranges       3
grapes        2
bananas       1

I am not looking for a pivot table solution, but rather a code in VBA.

Thanks in advance for all your help.

Current Code I am using the following:

'Setting Up Dynamic Array to Store Fruit Selections
Dim MyArray() As Variant


'Counting # of Rows
Dim lRow As Long
lRow = ws.Range("A13", ws.Range("A13").End(xlDown)).Rows.Count


'Resize Array
ReDim MyArray(lRow)


For i = 1 To lRow
    MyArray(x) = ws.Cells(i + 12, 11)
    x = x + 1
Next

Now that I have stored all the fruit values in the array, how can I count the number of unique fruits and their respective count?

Share what you have tried. If unsure consider using a dictionary. excelmacromastery.com/vba-dictionary . Add one to the value each time the key is encountered again. The keys are the fruit names. We can help with specific programming problems so sharing your attempts is important. — QHarr
– QHarr, Commented Aug 22, 2018 at 18:01
agreed, but do you know how I can do that if these values are stored in an array? — Ibra22
– Ibra22, Commented Aug 22, 2018 at 18:03
What have you tried? You can loop an array and use .Exists method of a dictionary. Without seeing any code I don't know whether array means the same thing to you as it does to me. — QHarr
– QHarr, Commented Aug 22, 2018 at 18:05
Thanks for your feedback. i have added a sample of the code I used. Let me know if you need more information. Happy to clarify. — Ibra22
– Ibra22, Commented Aug 22, 2018 at 18:08
FWIW doing Dim MyArray As Variant and then MyArray = Application.WorksheetFunction.Transpose(ws.Range("A13", ws.Range("A13").End(xlDown)).Value) would get you a single-dimensional array with all the values, without needing a loop and without needing to read each individual cell values - assuming lRow > 13. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 22, 2018 at 18:12

QHarr · Accepted Answer · 2018-08-22 18:28:34Z

1

Here is a dictionary method. I note you are using column A for finding last row but that your fruits, I believe, are in column K. Each time a key is encountered again in the dictionary 1 is added to the existing value for that key:

fruitDict(.Cells(i + 12, 11).Value) = fruitDict(.Cells(i + 12, 11).Value) + 1

Code:

Option Explicit
Public Sub TEST()
    Dim MyArray() As Variant, ws As Worksheet, lRow As Long, fruitDict As Object, i As Long
    Set fruitDict = CreateObject("Scripting.Dictionary")
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws
        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Select Case lRow
        Case Is < 13
            Exit Sub
        Case 13
            ReDim MyArray(1, 1)
            MyArray = .Range("A13").Value
        Case Else
            MyArray = .Range("A13:A" & lRow).Value
        End Select

        For i = LBound(MyArray, 1) To UBound(MyArray, 1)
            If Not IsEmpty(.Cells(i + 12, 11)) Then
                fruitDict(.Cells(i + 12, 11).Value) = fruitDict(.Cells(i + 12, 11).Value) + 1
            End If
        Next
    End With

    With Worksheets("Sheet2")
        .Range("A1").Resize(fruitDict.Count, 1) = Application.WorksheetFunction.Transpose(fruitDict.keys)
        .Range("B1").Resize(fruitDict.Count, 1) = Application.WorksheetFunction.Transpose(fruitDict.Items)
    End With
End Sub

edited Aug 22, 2018 at 18:28

answered Aug 22, 2018 at 18:21

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

5 Comments

Ibra22 Over a year ago

Oh that is amazing. Works like a charm. Honestly, still trying to reverse engineer how you did it but thank you for all the help!

QHarr Over a year ago

With the above, is there anything you would like explained it more detail?

Ibra22 Over a year ago

I have never used a dictionary method before and so I will have to do my own research, but will appreciate any high level explanation that you may have that can help me better understand and visualize the concept

QHarr Over a year ago

Have a look at the link I gave. Essentially it is a structure that has pairs of keys and values e.g. Key1:Value1, Key2:Value2. Keys must be unique.

QHarr Over a year ago

I am adding the fruits as the keys e.g."Apple":1, "Banana":1. And the initial value for each key is one (the first time of encountering the fruit). Each time I find the fruit again by matching on the key, I add another 1 to the existing value. So the value, at the end, matches the number of times the fruit is found.

Collectives™ on Stack Overflow

How to count repeating values in an array (VBA)?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related