Count unique words appearing in a column once per row

I would like to modify the below macro so it only counts a word once even if it appears more than once in a row. The reason I want to do this is so I can get a snapshot of the data without certain terms being over-represented.

For example, in the attached example the word 'Microsoft' appears 5 times in row 4 but I only want this to be counted once. In the whole data set Microsoft appears 20 times but only in 7 of the 20 rows so I would like the count to be 7.

The example is set out as follows: In column B there is a description field which in practice will contain consumer complaints and inquiries. To keep the data anonymous the description is filled out with random words and all other columns are blank. The output of Andy Pope's unique word counting macro appears in I:J.

Please Login or Register  to view this content.

I have been advised by a mod to start a new thread as the goals of my previous thread had changed.

Try this:

Please Login or Register  to view this content.

=SUMPRODUCT(--ISNUMBER(FIND("Microsoft", $B$4:$B$200)))

returns 7

The problem with not doing the count "as you go" using a formula like the one shown above..."Microsoft" may give the right answer, but some others would not.

If two of the words were "all" and "call", the formula shown applied to a search for "all" would also match on the columns with words like "Call", "allow", "small", etc.

This formulation checks for "whole word".

=SUMPRODUCT(--ISNUMBER(FIND(" all "," "&TRIM(SUBSTITUTE(SUBSTITUTE(B2:B200,","," "),"."," "))&" ")))

FWIW - I think the point is that the code retrieves all unique words appearing within the range whilst applying an adjacent count... as I see it JB's alterations match OP requirements.

Try this version... it also fixes the sort routine, removes ( ) ; : ' , " ] [

Please Login or Register  to view this content.

Just a question, are the words with numbers of any interest at all? Or are you really just after a count of the actual alphabetic word strings? We could use a simple technique to remove ALL characters from the strings that are not alphabetic.

In practice word strings with numbers may be of interest. Essentially this tool will help determine if there are any re-occurring terms of interest in a huge data set. Occasionally these terms may be things like company names or URLS that include a number, or sums of money or dates. I'll have to get back to you on how often these actually come up

However, upon looking at the data further it seems that a result like "efficient6" has been pulled when is isn't actually a whole word in the text. It appears as "efficient.[6]". It would be preferred if these kind of results would not appear.

I appreciate your interest.

Your expanded requirements demonstrate the black hole these kinds of projects can really be. The human mind is a powerful analytical tool, trying to get Excel to do it for you can be daunting.

"Remove decimals, except not always."
"All words are separated by spaces, but not always, so sometimes removing punctuation will accidentally merge two words, do that differently...(begin litany of variations on what to do)"
"Be sure not to remove special characters from email addresses"

...and on and on.

It may be time to hire someone to oversee what I imagine will continue to be an ongoing utility development.