CODEWITHSUNDEEP
exceltextcountsum
Michael Neiman
Michael Neiman

Reputation: 1

How to count unique rows that have 1 of many text strings?

I have a large table with multiple different combinations of text strings in a column, and i am trying to get a count of unique rows that include some values mixed in the text strings. For example:

Category   Items
A          Apple, Orange, Frog
B          Tree, Rock
C          Banana, Orange, River, Monkey
D          River, Beaver, Horse
E          Monkey, Banana

I want to find an excel formula that will evaluate the table above and return the number of unique rows that include any of the fruits (e.g. Apple, Orange, or Banana). In this case it would be Categories A, C, and E ... for a total of 3 rows. My actual list of text strings to look for is ~100 (e.g. apple, banana, orange, kiwi, strawberry, melon, grape, etc.)

Upvotes: 0

Views: 105

Answers (1)

Soulfire
Soulfire

Reputation: 4296

I couldn't conceive of this as a regular Excel function. I have no idea if you're interested in a VBA solution, but I gave it a shot anyways.

To use this function:

  • Open the VB Editor by pressing Alt+F11 (or under the Developer tab select 'Visual Basic')
  • Right Click 'Modules' and then Insert --> Module

  • Paste the following code into the module you just created

    Function FindWords(List As String, Delimiter As String, ArrayIn As Range)

    Dim Words As Variant
    Dim NumUnique As Long
    Dim Element As Range

    Words = Split(List, Delimiter)

    NumUnique = 0

    Dim i As Integer
    For i = LBound(Words) To UBound(Words)
        For Each Element In ArrayIn
            If InStr(1, Element.Value, Words(i), vbTextCompare) > 0 Then
                NumUnique = NumUnique + 1
            End If
        Next Element
    Next i

    FindWords = NumUnique

End Function

You can then use this function like any other Excel function. In whichever cell you want the count of unique rows to appear you would just type =FindWords(List, Delimiter, ArrayIn).

The List is a delimited string of values you want to look up. In your example you would pass in whichever cell contains the value Apple, Orange, Banana.

The Delimiter is whichever item delimits your list. In your example the delimiter is a comma with a space and so ", " should be passed in.

The ArrayIn variable is the range of cells you want to look in for the items in the list. In your example, assuming Category was in cell A1 you would pass in $B$2:$B$6 and it will look through all of those cells.

Let's say your list of 100 words is in cell D1, then the function would be called like so: =FindWords(D1,", ",$B$2:$B$6)

Upvotes: 0

Related Questions