Get congruent cell counts with same value.

[Cyndie_Birdsong]

I am trying to figure out how to get a total count of congruent cells with the same value in each row. (the color doesn't matter, just whether there's an X or empty cell) I need to create text that will show something like the following:

The text for the top row would say something like

"3 empty cells, 1 X cell, 2 empty cells, 5 X cells, 2 empty cells, 1 X cell, 3 empty cells"

or whatever verbiage would work to show each congruent set of values. (although the value is text & not numeric) And then so on for each row..

I was hoping there would be some type of function for congruent cell value counts, similar to how there are counts based on cell color. 

Thanks!!

[#2]

@Cyndie_Birdsong 

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Function Describe(rng As Range) As String
    Dim i As Long
    Dim f As Boolean
    Dim n As Long
    Dim s As String
    n = 1
    f = (rng(1).Value <> "")
    For i = 2 To rng.Count
        If rng(i).Value = "" Then
            If f Then
                s = s & ", " & n & " X cell" & IIf(n = 1, "", "s")
                f = False
                n = 1
            Else
                n = n + 1
            End If
        Else
            If f Then
                n = n + 1
            Else
                s = s & ", " & n & " empty cell" & IIf(n = 1, "", "s")
                f = True
                n = 1
            End If
        End If
    Next i
    If f Then
        s = s & ", " & n & " X cell" & IIf(n = 1, "", "s")
    Else
        s = s & ", " & n & " empty cell" & IIf(n = 1, "", "s")
    End If
    Describe = Mid(s, 3)
End Function

Switch back to Excel.

Let's say the first row with data is A1:Q1.

In another cell in row 1, for example R1, enter the formula

=Describe(A1:Q1)

Fill or copy down.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open the workbook.

[#3]

@Hans Vogelaar Thanks a lot!! I'll give it a try & let you know how it goes. I appreciate your quick reply!! :)

[#4]

@Hans Vogelaar It worked like a charm! Thanks so much!! :)