Forum Discussion

Cyndie_Birdsong's avatar
Cyndie_Birdsong
Copper Contributor
Nov 07, 2020

Get congruent cell counts with same value.

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!!

 

Formulas and Functions

3 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 07, 2020

    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.

Resources