Counting multiple occurrences

Question

I am looking for a formula to list occurrences of values only if they are greater than 2 times; and the result would be shown as in the image.

For example, if a value repeats 2 times, it's shown by "2", and 3 times by "3". so if there are two numbers repeating in the range, then it would be shown by "32" as in the image below. (There is no need for a comma between the numbers). Thanks.

I can get you the pattern with formulas, `22122111`,`33322111` and `44411411`. But I think you will need vba to get the answer you are seeking. — Scott Craner, Aug 29 '17 at 17:58
My first choice would be a formula if possible. but I am ok with a vba solution, too. — Max, Aug 29 '17 at 18:08
If the number of times a number may repeat is fixed you can get there if a super long formula. — David Lee, Aug 29 '17 at 18:12
I'm surprised that this is a too complex for a formula actually. — Max, Aug 29 '17 at 18:20

score 2 · Accepted Answer · answered Aug 29 '17 at 18:12

2

Here is a simple UDF:

Function mycount(rng As Range) As String

Dim str As String
Dim rngcnt As Range
For Each rngcnt In rng
    If InStr("," & str & ",", "," & rngcnt.Value & ",") = 0 Then
        If Application.WorksheetFunction.CountIf(rng, rngcnt) > 1 Then
            mycount = mycount & Application.WorksheetFunction.CountIf(rng, rngcnt)
            str = str & "," & rngcnt
        End If
    End If
Next rngcnt
End Function

So your call on the sheet would be:

=mycount(A2:H2)

Then copy down.

answered Aug 29 '17 at 18:12

Scott Craner

148,073
10
49
81

Scott, this works great. thanks. If I wanted to add a letter to the end of the count, say "r", to make it look like 22r, 32r, 4r etc. what should I add to the vba? I can play with formulas but not much with vba. – Max Aug 29 '17 at 18:18
You can put it in the code or just use `=mycount(A2:H2) & "r"` as the formula @Max. As a note: please next time put that requirement in the original post. Please remember to mark as correct. – Scott Craner Aug 29 '17 at 18:20
yes, I'm sorry, I was expecting a formula, I forgot about it with vba. thanks. – Max Aug 29 '17 at 18:23
Thanks. NIzam. you didn't need to delete it though. you can keep it as an alternative if you want. it would be good for VBA learners to compare to codes,too. – Max Aug 29 '17 at 18:25
Thanks. My answer got much bigger also because I sort the characters after discovering them. – Nizam Aug 29 '17 at 18:27

score 2 · Answer 2 · answered Aug 29 '17 at 18:22

The way I got it is defining a VBA function.This function uses a dictionary, so it is necessary to add th reference to 'Microsoft Scripting Runtime' (look here). Also, I have used a function to sort the characters in string from here

Function Repetitions(rng As Range)

    Dim dict As New Scripting.Dictionary
    Dim res() As Integer

    For aux = 1 To rng.Count
        Dim numero As Integer
        numero = rng.Cells(1, aux).Value
        If Not dict.Exists(numero) Then
            dict.Add numero, 1
        Else
            dict(numero) = dict(numero) + 1
        End If
    Next aux

    Dim result As String
    result = ""
    For aux = 0 To UBound(dict.Items)
        If dict.Items(aux) > 1 Then result = result & dict.Items(aux)
    Next aux

    While Len(result)
        iTemp = 1
        Temp = Left(result, 1)

            For I = 2 To Len(result)
            If StrComp(Mid(result, I, 1), Temp, vbTextCompare) = 0 Then
                If StrComp(Mid(result, I, 1), Temp, vbBinaryCompare) = 1 Then
                    Temp = Mid(result, I, 1)
                    iTemp = I
                End If
            End If

            If StrComp(Mid(result, I, 1), Temp, vbTextCompare) = 1 Then
                    Temp = Mid(result, I, 1)
                    iTemp = I
                End If
            Next I

        Repetitions = Repetitions & Temp
        result = Left(result, iTemp - 1) & _
                            Mid(result, iTemp + 1)
    Wend


End Function

After all, you will be able to use the function as formula in Excel, calling it as following for example:

=Repetitions(A2:F2)

Counting multiple occurrences

2 Answers2