Counting the repeating values and displaying with alphanumeric symbols

[Usercode]

Hello,

I am not sure if this can be done with a formula, but if it is, I would appreciate the help.

I have a set of data some of which can be repeated several times in the same row. And if the values are repeated, I'd like them to be displayed with alphanumeric symbols. For example, If data is (1,1,1,3,3,4,5), then the output should be 32R. (three 1s, two 3s). Since 4,5 and are not repeated, they are excluded. if data is (1,2,2,3,3,4,5), then the output should be 22R (two 2s and two 3s).

I have attached an example worksheet. Thanks!

https://1drv.ms/x/s!AoGkZUHlKui9gSEHkSskV6ynfmAJ

 

[Aladin Akyurek]

In I2 control+shift+enter, not just enter, and copy down:

=IF(FREQUENCY(A2:G2,A2:G2)>1,TEXTJOIN("",TRUE,IF(FREQUENCY(A2:G2,A2:G2)>1,FREQUENCY(A2:G2,A2:G2),""))&"R","")

 

[Usercode]

I tried the formula with control+shift+enter, but I got #NAME ERROR. I have office 2007, any suggestions please?

 

[Usercode]

Thanks Aladin!! great work. I found the solution for textjoin on the internet.

 

[Aladin Akyurek]

I tried the formula with control+shift+enter, but I got #NAME ERROR. I have office 2007, any suggestions please?

Thanks Aladin!! great work. I found the solution for textjoin on the internet.

You are welcome.

 

[Usercode]

Hi Aladin, I am sorry, but I'm having a problem with the code. It sometimes just doesn't work. It gives the correct results sometimes, and sometimes not, just leave the cell blank.
I share a file here https://1drv.ms/x/s!AoGkZUHlKui9gSKZUZSIptCYNugl as you can see I highlighted the values that are not processed in yellow color. Do you have any suggestion why I have this issue ? I use excel 2007.

 

[Aladin Akyurek]

My bad...

Control+shift+enter, not just enter, and copy down:

=IF(SUM(IF(FREQUENCY(A1:F1,A1:F1)>1,1)),TEXTJOIN("",TRUE,IF(FREQUENCY(A1:F1,A1:F1)>1,FREQUENCY(A1:F1,A1:F1),""))&"c","")

Does this fit your expected outcome?

 

[Usercode]

I tried it but it didn't change the result on my worksheet.

 

[Usercode]

I have excel 2007. and I use this vba script for textjoin function :

Function Textjoin(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0


    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    Textjoin = Textjoin & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                Textjoin = Textjoin & arr2(c) & delim
            End If
        Next c
    End If
    Textjoin = Left(Textjoin, Len(Textjoin) - Len(delim))
End Function

 

[Aladin Akyurek]

I tried it but it didn't change the result on my worksheet.

I have excel 2007. [...]

Control+shift+enter, not just enter, and copy down:

=IF(SUM(IF(FREQUENCY(A1:F1,A1:F1)>1,1)),aconcat(IF(FREQUENCY(A1:F1,A1:F1)>1,FREQUENCY(A1:F1,A1:F1),""))&"c","")

For this formula to work, a

dd ​

the following code as a module to your workbook using Alt+F11...
​

Function ​

aconcat​

(a As Variant, Optional sep As String = "") As String​

​

' Harlan Grove, Mar 2002​

​

Dim y As Variant​

​

If TypeOf a Is Range Then​

​

For Each y In a.Cells​

​

aconcat​

= ​

aconcat​

& y.Value & sep​

​

Next y​

​

ElseIf IsArray(a) Then​

​

For Each y In a​

​

aconcat​

= ​

aconcat​

& y & sep​

​

Next y​

​

Else​

​

aconcat​

= ​

aconcat​

& a & sep​

​

End If​

​

aconcat​

= Left(​

aconcat​

, Len(​

aconcat​

) - Len(sep))​

​

End Function​