Key Word Lookups and Quantities

[Nocean]

Hi All,

I am new to this forum, but just had to post as I am struggling having spent many hours searching, I cannot find a suitable answer for my needs so hope I have explained enough below.

Situation: I have a document containing many many words, like a specification or book, for which I need to identify the number of words which match a pre-defined list of words or "key words" and how many times they occur from selected pages.

In other words a key word search, hi-lighting the word found and the number of times used.

I have copied and pasted (from word into excel) splitting the original document in the workbook into rows and columns with each cell generally containing a word, symbol or characher.

The lookups/arrays I have played with do not seem to work when I used the Defined Name Table I created for the "key words" (called keywords) against the Defined Name Table I created for the document (called spec) to be compared.

I have tried individual column Defined Table Names also with no success and am no good at all with VBA!

Can anyone assist me please I need a solution quickly, there seems to be a wealth of knowledge and skills on this forum for which I am confident an answer will be forth coming.

Thanks in advance

 

[bryonwoods30]

if each of your cells only contains one word or symbol use the countif function to lookup your key works

=countif(range,value to count instances of)

 

[bryonwoods30]

as for the highlighting...

just highlight all your cells, go to format, conditional formatting, then say cell is equal to and select the cell containing your key word and choose the formatting options you want.

 

[Nocean]

Thanks, I will try your suggestion which seems a lot more simple than what I was trying to carry out.

As for the conditional formatting, thats not a problem, I am good with this aspect!!!

Really appreciate your prompt response will let you know how I get on.

 

[Nocean]

if each of your cells only contains one word or symbol use the countif function to lookup your key works

=countif(range,value to count instances of)

This does work from a quick sample undertaken, however when I double check using the find replace option, there is a discrepancy between the formula results and the actual number of tme the word is in the document.

Example: Word to find is Facilities, in the Find and Replace search this word appears 11 times identifying lower and upper case versions. Other word are OK, can you advise why this is happening?

 

[bryonwoods30]

countif counts them if they are upper or lowercase as long as they are spelled exactly the same the key word. Since you imported from word into excel, what you might want to do is highlight all the cells you are counting and do a find and replace and replace spaces with "" incase any spaces are in the cells because the word Dog with a space in the cell wouldn't match your keyword dog without a space

 

[Nocean]

I have spotted my error, not searchinh the same number sof cells and rows against my sample ..doh!

All sorted and working well thank you. And so much more simple than my attempts.

Really appreciated your help.