Distinguish and select unique words

[Kilgore_elder]

Dear Forum,
I have a frustrating problem and need help. I have a worksheet where it identifies the number of times keywords appear in a list in Column A. The worksheet then identifies the keywords and lists them in Column F. each separate instance of the words has a corresponding number in Column B. Each instance of the word's number appears in a row in Columns H to AK. This allows for up to 30 instances of a word/number. The available numbers are then totalled in Column AL and this number is then divided by the number of instances the word appears in the list, which is listed in Column G. The word from Column F is copied to Column AN.
Column F is an array that uses a UDF (ListOfWords) and Columns H to AK also use an array.
The Column F array is: {=ListOfWords($A$1:$A$5000, FALSE)}
Column G formula is: =COUNTIF($A$1:$A$5000,$F1) (this increments down the column)
Columns H to AK array is: {=INDEX($B$1:$B$5000, SMALL(IF(ISNUMBER(SEARCH($F$1, $A$1:$A$5000)), MATCH(ROW($A$1:$A$5000), ROW($A$1:$A$5000))), ROW($A$1)))} This array increments along the columns and down the rows.
What I have found is that it is counting instances where the word, i.e. 'Requirement', is also including the count of words such as require. This means that columns H to AK are including the numbers associated with instances of the word 'require' as well as 'requirement'. What I need is the ability to count whole words only and not substrings of words.
It would be better to provide a sample worksheet, but I can't work out how I post it.
Any assistance would be greatly appreciated.
Thanks in anticipation,
Kilgore_elder

 

[yky]

My guess is "SEARCH($F$1, $A$1:$A$5000)" is the culprit. Search will match substring. So, if you are searching for "require", "requirement" is a match because it contains the substring "require".

Try "MATCH($F$1, $A$1:$A$5000,0)" see how it works.

 

[Kilgore_elder]

Thanks for the suggestion yky. I gave it a try and that configuration just repeats the first number over and over. MATCH is present in the array after the SEARCH function, so I may have done it wrong.

 

[wideboydixon]

I suspect you'll need another UDF. It depends on how ListOfWords() works to identify individual words from the text in column A. Could you post the code for that UDF and then I may be able to help you. Please use code tags.

WBD

 

[Kilgore_elder]

Hi wideboydixon,
Thanks for responding. Here is the code, though I don't think this is the problem. It generates a list of unique words using the array I originally posted. I suspect it is the array in Columns H to AK

Function ListOfWords(rng As Range, Optional CaseSensitive As Boolean) As Variant
'This creates the ListOfWords function
Dim X As Long, Index As Long, List As String, Words() As String, LoW As Variant
With WorksheetFunction
    Words = Split(.Trim(Replace(Join(.Transpose(rng)), Chr(160), " ")))
    LoW = Split(Space(.Max(UBound(Words), Application.Caller.Count) + 1))
For X = 0 To UBound(Words)
    If InStr(1, Chr(1) & List & Chr(1), Chr(1) & Words(X) & Chr(1), 1 - Abs(CaseSensitive)) = 0 Then
        List = List & Chr(1) & Words(X)
    If CaseSensitive Then
          LoW(Index) = Words(X)
    Else
            LoW(Index) = StrConv(Words(X), vbProperCase)
    End If
            Index = Index + 1
    End If
Next
    ListOfWords = .Transpose(LoW)
End With
End Function

Thanks for the assistance.

 

[yky]

OK. Then, try this one:

{=INDEX($B$1:$B$5000, SMALL(IF(EXACT($F$1, $A$1:$A$5000), MATCH(ROW($A$1:$A$5000), ROW($A$1:$A$5000))), ROW($A$1)))}

 

[Kilgore_elder]

Thanks yky. That seems to have solved my problem. Thanks for all your help.

 

[Rick Rothstein]

Here is the code, though I don't think this is the problem. It generates a list of unique words using the array I originally posted. I suspect it is the array in Columns H to AK

Function ListOfWords(rng As Range, Optional CaseSensitive As Boolean) As Variant
'This creates the ListOfWords function
Dim X As Long, Index As Long, List As String, Words() As String, LoW As Variant
With WorksheetFunction
    Words = Split(.Trim(Replace(Join(.Transpose(rng)), Chr(160), " ")))
    LoW = Split(Space(.Max(UBound(Words), Application.Caller.Count) + 1))
For X = 0 To UBound(Words)
    If InStr(1, Chr(1) & List & Chr(1), Chr(1) & Words(X) & Chr(1), 1 - Abs(CaseSensitive)) = 0 Then
        List = List & Chr(1) & Words(X)
    If CaseSensitive Then
          LoW(Index) = Words(X)
    Else
            LoW(Index) = StrConv(Words(X), vbProperCase)
    End If
            Index = Index + 1
    End If
Next
    ListOfWords = .Transpose(LoW)
End With
End Function

Wow, that code looks exactly like how I would have written it, but I did a search of this forum and cannot find it. Just out of curiosity, do you remember where you got that function from?

 

[Kilgore_elder]

I think I found it on another forum, but it was named FindUniqueWords, I believe. I renamed it to make it easier for me when identifying and using it.

 

[Rick Rothstein]

I think I found it on another forum, but it was named FindUniqueWords, I believe. I renamed it to make it easier for me when identifying and using it.

Interesting... as I said, that is the exact way I would have written it, down to the names of the variables (except that I usually would have capitalized the R in the 'rng" argument name). Thanks for getting back to me.