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

#### bythecshore

##### Board Regular
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.

Last edited:

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hello,

Written in C and D columns.

Code:
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
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

Last edited:
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.

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.

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.

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.

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.

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?

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.

Last edited:
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?

Code:
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
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

Replies
2
Views
96
Replies
1
Views
710
Replies
3
Views
139
Replies
15
Views
224
Replies
3
Views
91

1,203,072
Messages
6,053,377
Members
444,659
Latest member
vuphihung

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back