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.

 

[Rick Rothstein]

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.

I think this should get you a list close to what you want...

Sub CountResponses()
  Dim X As Long, Z As Long, LastRow As Long, CellVal As String, Dict As Object, Words() As String
  Const DataColumn As String = "A"
  Const OutputColumn As String = "C"
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  Set Dict = CreateObject("Scripting.Dictionary")
  Columns(OutputColumn).Resize(, 2).Clear
  For X = StartRow To LastRow
    CellVal = Cells(X, DataColumn).Value
    For Z = 1 To Len(CellVal)
      If Mid(CellVal, Z, 1) Like "[!A-Za-z0-9#, ]" Then Mid(CellVal, Z) = ","
    Next
    If InStr(CellVal, ",") Then
      Words = Split(CellVal, ",")
    Else
      Words = Split(CellVal)
    End If
    For Z = 0 To UBound(Words)
      If Not Dict.exists(Trim(Words(Z))) Then
        Dict.Add Trim(Words(Z)), 1
      Else
        Dict.Item(Trim(Words(Z))) = Dict.Item(Trim(Words(Z))) + 1
      End If
    Next
  Next
  With Cells(StartRow, OutputColumn)
    .Resize(Dict.Count) = WorksheetFunction.Transpose(Dict.keys)
    .Offset(, 1).Resize(Dict.Count) = WorksheetFunction.Transpose(Dict.items)
    .Resize(Dict.Count, 2).Sort Key1:=.Offset(, 1), Order1:=xlDescending
  End With
  Set Dict = Nothing
End Sub

Note: You can adapt the code to your actual data structure using the three constants (the Const statements), just change my example values to your actual setup values.

 

[bythecshore]

I hate to sound ignorant, but how do I use/impliment the code? I'm pretty good at this stuff, just never done that before. Can you point me to an instruction sheet of how to do this?

 

[Rick Rothstein]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (CountResponses) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.