Phrase counter

[dorkus]

Hi All,

I would like to be able to count the frequency of specified phrases in the substrings of cells in a column.

Requirement
In the above example, we would paste values into column A and then based on a list of phrases in column C the frequency of those phrases would be listed in column E.
(As a futher level of sophistication, is it possible to to count "Fox Jumped" and "foxes jump" as the same value (so in the example above the frequecy of "fox jump" would display a frequency of 2? )

Statement list

Statements

The quick brown fox jumped over the happy dog

Is a wagging tail a sign of a happy dog?

The customer did not follow the correct process

My colleague did not follow the correct process

the advice on the website does not show the correct process

Parent is unhappy with level of home work

Parents will be invited to the play

Foxes jump over thehappy dog

Phrase List

happy dog

correct process

home work

fox jump

Many thanks in advance

 

[jasonb75]

In E2 and fill down, one of

=SUM(COUNTIF(A:A,{"","","* ","* "}&C2*{""," *"," *",""}))

=COUNTIF(A:A"*"&SUBSTITUTE(C2," ","*")&"*")

The first will only count exact matches, so jump and jumped will not be counted as the same.

I haven't tested the second formula, if it does work, in addition to your examples it will also count Foxes watch happy dogs jumping
It will not be possible to exclude this and count the similarities that you asked for.

 

[dorkus]

In E2 and fill down, one of

=SUM(COUNTIF(A:A,{"","","* ","* "}&C2*{""," *"," *",""}))

=COUNTIF(A:A"*"&SUBSTITUTE(C2," ","*")&"*")

The first will only count exact matches, so jump and jumped will not be counted as the same.

I haven't tested the second formula, if it does work, in addition to your examples it will also count Foxes watch happy dogs jumping
It will not be possible to exclude this and count the similarities that you asked for.

The first line didn't work but after adding a comma to your second line the result is exactly what I was looking for. Thank you so much!