How many times do all words appear in a range of cells, ranked in order?

[bythecshore]

I have a range (a column) of about 400 cells, each with a bunch of words. Some have one word, some have three or four. What I want to do is have Excel count all the words, and then give me a list, with the most-used words at the top.

For example, cells may have the word "red," or "blue, green, red, yellow," or "red, green, orange." In this case, the list I want would look like this:

Red 3
Green 2
Blue 1
Orange 1
Yellow 1

How would I do this? I'm stumped.

 

[Omer Baris]

Hello,

Written in C and D columns.

Sub Report()

    Dim d As Object, i As Long, sat As Long, deg, s, a1, a2

    Set d = CreateObject("Scripting.Dictionary")
    
    Range("C2:D" & Rows.Count).ClearContents
    
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        deg = Cells(i, "A")
        If Not d.exists(deg) Then
            s = 1
            d.Add deg, s
        Else
            s = d.Item(deg)
            s = s + 1
            d.Item(deg) = s
        End If
    Next i
    
    a1 = d.keys: a2 = d.items: sat = 2
    
    For i = 0 To d.Count - 1
        Cells(i + sat, "C") = a1(i)
        Cells(i + sat, "D") = a2(i)
    Next i

End Sub

 

[mecg96]

Assuming your data is in Column A, Rows 1 to 400.
Say you add in column B the list of Colors you want to count... Cell B1 = Red, Cell B2= Green, Etc... then in Col C add this formula =COUNTIF($A$1:$A$400,"*"&B1&"*") next to the colors you just typed in. The formula should count how many times the word in column B appears in column A.

 

[Rick Rothstein]

Assuming your data is in Column A, Rows 1 to 400.
Say you add in column B the list of Colors you want to count... Cell B1 = Red, Cell B2= Green, Etc... then in Col C add this formula =COUNTIF($A$1:$A$400,"*"&B1&"*") next to the colors you just typed in. The formula should count how many times the word in column B appears in column A.

Just to add a clarification to mecg96's message... the posted formula will count the number of cells that the word appears in, not (necessarily) the number of times the word appears in the cells. For example, if a cell contains this text... "red, blue, green, red, yellow"... the word red appears twice in the cell, but the above formula will only count 1 for that cell because it is looking only to see if red appears in the cell anywhere, not how many times it appears in the cell. My guess is your data does not have repeats, so the formula should work fine for you, but I just wanted to give you a "heads up", just in case.

 

[bythecshore]

I'm actually trying to do with *without* having to list the "colors," because I'm not really trying to count colors, it's a little more complicated.

The actual application here is that I have 400 answers to a question from a poll that our company did, so there are many different answers. It was an open ended question, so there may be 50 or more different answers. I don't have a list of all of them, and even if I did, the formula would be so long it would be impossible to work with.

So I need Excel to look at the list, extract one instance of any word used at least one, then count how many times it appears, and then rank the list with the most used words at the top.

 

[Rick Rothstein]

So I need Excel to look at the list, extract one instance of any word used at least one, then count how many times it appears, and then rank the list with the most used words at the top.

Will the answers have words in them like "a", "the", "I", "their", "these", as the like? If so, there is a good chance they will dominate the top of the outputted list.

 

[bythecshore]

Will the answers have words in them like "a", "the", "I", "their", "these", as the like?

No, not really. The question was "what workplace issues are most important to your company?" so most of the answers are one or two words, e.g., Quality, Teamwork, Accident prevention," etc. So I know that the list I want won't be perfect, but it'll get me 95% of the way there, that is, to identifying respondent's primary concerns.

 

[Rick Rothstein]

No, not really. The question was "what workplace issues are most important to your company?" so most of the answers are one or two words, e.g., Quality, Teamwork, Accident prevention," etc. So I know that the list I want won't be perfect, but it'll get me 95% of the way there, that is, to identifying respondent's primary concerns.

Assuming commas are used to delineate the list (that will be the case, right?), did you want "accident prevention" to be considered as if it were a "single word" or did you want "accident" and "prevention" to be listed separately in the list?

 

[bythecshore]

Some have commas, some don't, some have dashes between words. Unfortunately, there are all kinds of random users (customers) and they all have their own styles. But if there is a comma, I'd like to get all the words between them as one term as opposed to separate words.

 

[Omer Baris]

For example, cells may have the word "red," or "blue, green, red, yellow," or "red, green, orange." In this case, the list I want would look like this:

Red 3
Green 2
Blue 1
Orange 1
Yellow 1

Is this what you want?

Sub Report()

    Dim d As Object, i As Long, rw As Long, j As Integer, b, s, a1, a2

    Set d = CreateObject("Scripting.Dictionary")
    
    Application.ScreenUpdating = False
    Range("C2:D" & Rows.Count).ClearContents
    
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        b = Split(Cells(i, "A"), ",")
        For j = 0 To UBound(b)
            If Not d.exists(Trim(b(j))) Then
                s = 1
                d.Add b(j), s
            Else
                s = d.Item(Trim(b(j)))
                s = s + 1
                d.Item(Trim(b(j))) = s
            End If
        Next j
    Next i
    
    a1 = d.keys: a2 = d.items: rw = 2
    
    For i = 0 To d.Count - 1
        Cells(i + rw, "C") = a1(i)
        Cells(i + rw, "D") = a2(i)
    Next i
    
    Range("C2:D" & Rows.Count).Sort Key1:=Range("D2"), Order1:=xlDescending
    
    Set d = Nothing
    Application.ScreenUpdating = True

End Sub