How to find and count matching text in excel

[jenwren]

Hello,


I am looking for a formula that can find and count repeated matching text in a column, whilst weeding out text like 'a' or 'the'. I am logging feedback comments from forms and want to be able to find a way of flagging up repeated words/comments that are coming up. I've tried using duplicate values in conditional formatting but this is toot exact. Can anyone advice please?


Thank you

 

[Luke M]

Could you give an example of what you mean? Are you looking at a complete cell for a specific word, within a cell for a specific word/phrase, a word/phrase repeated within a cell?

 

[jenwren]

I am using a feedback form that asks for three positive and three negative comments and I am logging these in a column, I am trying to flag up where repeated words or phrases come up, for example - 'air conditioning' or 'car parking' within these comments so that when the conferencing manager takes a look it is highlighted if an issue has come up a number of times. Hope that makes sense!

 

[Luke M]

If you have the phrase already defined in a cell somewhere, I suppose you could do this:

=SUMPRODUCT(1*ISNUMBER(SEARCH(CellWithPhrase,FeedbackCells)))


That would give you the count of how many times a phrase appears. Having XL try to determine on it's own what phrases are repeated is much more challenging, especially since it would involve combinations of words (each additional word makes it exponentially harder) and would need to ignore common words (a, an, and, ...)


You might also try searching Google for "excel cloud tags". I haven't done much with them myself, but I know Chandoo uses them for the forum. Might have some leads that way.

 

[jenwren]

Hi LukeM,


Thank you for your help I'll see what I can do with your suggestions, do appreciate you having a look at it.


Regards.

 

[SirJB7]

Hi, jenwren!

Give a look at this link:

http://www.excelforum.com/excel-programming-vba-macros/680810-create-tag-cloud-in-vba-possible.html

Regards!