Counting the repeating values and displaying with alphanumeric symbols

Usercode

Board Regular
Joined
Aug 18, 2017
Messages
107
Office Version
  1. 2016
Platform
  1. Windows
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
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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","")
 
Upvote 0
I tried the formula with control+shift+enter, but I got #NAME ERROR. I have office 2007, any suggestions please?
 
Upvote 0
Thanks Aladin!! great work. I found the solution for textjoin on the internet.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
I tried it but it didn't change the result on my worksheet.
 
Upvote 0
I have excel 2007. and I use this vba script for textjoin function :

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top