SOLVED

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

Copper Contributor

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 B

Desired result: 1xD, 3xB, 1xR, 1xG, 4xB, 3xO, 2xB

I 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

2 Replies
best response confirmed by Jungdra (Copper Contributor)
Solution

@Jungdra 

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 > 1 Then
                s = s & ", " & n & "x" & c
            End If
            c = rng(i).Value
            n = 1
        End If
    Next i
    s = s & ", " & n & "x" & c
    CountLetters = Mid(s, 3)
End Function

Let's say the first row with letters is A2:O2.

Enter the following formula in P2:

 

=CountLetters(A2:O2)

 

Fill down to the last row with letters.

 

Hallo Hans,
entschuldigung, ich hatte eine Benachrichtigung bei Antwort erwartet, aber es kam keine. Habe Deine Lösung gerade erst entdeckt. Freue mich drauf sie auszuprobieren, aber das muss ein paar Tage warten. Vielen lieben Dank schon mal vorab!
Viele Grüße,
Claudia
1 best response

Accepted Solutions
best response confirmed by Jungdra (Copper Contributor)
Solution

@Jungdra 

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 > 1 Then
                s = s & ", " & n & "x" & c
            End If
            c = rng(i).Value
            n = 1
        End If
    Next i
    s = s & ", " & n & "x" & c
    CountLetters = Mid(s, 3)
End Function

Let's say the first row with letters is A2:O2.

Enter the following formula in P2:

 

=CountLetters(A2:O2)

 

Fill down to the last row with letters.

 

View solution in original post