calculate total occurrence of a keyword(s)

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

This is for a survey comments exported to Excel. I'd like to tally the
total occurrence of certain keywords in cells. sumproduct come into
mind but it capture the total of an exact entry. Is there a way I can
add up all the cells that contain a or multiple keywords I indicate.

TIA
 
Some examples would help
Click to expand...

For example:
A1: "give me the job aids"
A2: "nothing"
A3: "more clips"
A4: "call clips are helpful"
A5: "no need to change"
A6: "I like to see examples"
A7: "examples"
A8: "I like what it is now."
A9: "cheatsheets"
A10: "it's useless"
A20: "not really useful"

It would be nice if I can find the total occurence of cells that
contain "clips" (= 2) or "examples" (=2) from the whole column.
Because everyone writes differently, it won't be an exact match. I
can't just use sumproduct to match "clip". It would be even better if
I have multiple criteria, eg. find total cells matching of either
"like" or "nothing"

Thanks for the help,
 
Hi,
Try this macro

Sub Macro1()
B = 0
Range("A1").Select
Cells.Find(What:=Range("E1")).Activate
B = B + 1
A = ActiveCell.Address
Cells.FindNext(After:=ActiveCell).Activate
Do Until ActiveCell.Address = A
Cells.FindNext(After:=ActiveCell).Activate
B = B + 1
Loop
MsgBox (B & " instances of <" & Range("E1") & ">")
End Sub

Enter the word you want to find into E1, then run.

Regards - Dave.
 
Back
Top