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
– Scott Craner, Commented Aug 29, 2017 at 17:58
My first choice would be a formula if possible. but I am ok with a vba solution, too. — Max
– Max, Commented Aug 29, 2017 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
– David Lee, Commented Aug 29, 2017 at 18:12
I'm surprised that this is a too complex for a formula actually. — Max
– Max, Commented Aug 29, 2017 at 18:20

Scott Craner · Accepted Answer · 2017-08-29 18:12:14Z

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, 2017 at 18:12

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

5 Comments

Max Over a year ago

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.

Scott Craner Over a year ago

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.

Max Over a year ago

yes, I'm sorry, I was expecting a formula, I forgot about it with vba. thanks.

Max Over a year ago

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.

Nizam Over a year ago

Thanks. My answer got much bigger also because I sort the characters after discovering them.

Nizam · Accepted Answer · 2017-08-29 18:22:08Z

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)

Collectives™ on Stack Overflow

Counting multiple occurrences

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related