Sep 26 2020 08:01 AM
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
Sep 26 2020 08:39 AM
SolutionCreate 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.
Oct 01 2020 12:00 AM
Sep 26 2020 08:39 AM
SolutionCreate 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.