Count in a row until content changes, then count this next content until it changes and so on

Question

Hello Community,it is easier to see what I am looking for, than to explain. I have a table with 15 columns and a lot of rows. In each column is a letter and in a row, they change a lot. They are not (and should not be) in an alphabetical order. It is a created pattern for working with pearls. I would like to summarize each row as follows:Example row: D B B B R G B B B B O O O B BDesired result: 1xD, 3xB, 1xR, 1xG, 4xB, 3xO, 2xBI do NOT search the result like 1xD, 9xB, etc... I need the count of the letters in the correct order.In the end I will need the VBA Code, but if I would know the correct functions which I have to combine, this would be an appreciated start.Thanks very much and best regards,Claudia

hansvogelaar · Accepted Answer

Jungdra&nbsp;
Create the following custom VBA function:
Function CountLetters(rng As Range) As String
    Dim i As Long
    Dim s As String
    Dim c As String
    Dim n As Long
    
    c = rng(1).Value
    For i = 1 To rng.Count
        If rng(i).Value = c Then
            n = n + 1
        Else
            If i &gt; 1 Then
                s = s &amp; ", " &amp; n &amp; "x" &amp; c
            End If
            c = rng(i).Value
            n = 1
        End If
    Next i
    s = s &amp; ", " &amp; n &amp; "x" &amp; c
    CountLetters = Mid(s, 3)
End Function
Let's say the first row with letters is A2:O2.
Enter the following formula in P2:
&nbsp;
=CountLetters(A2:O2)
&nbsp;
Fill down to the last row with letters.
&nbsp;

Forum Discussion

Count in a row until content changes, then count this next content until it changes and so on

2 Replies

Resources