Common Words in Excel

[Welsh Mark3]

I have a list of about 8000 company names, I am trying to identify the keywords that appear in this list. Does anyone have any recommendations?

For Example

Mark Edwards Supply incRick Cook Supply
America Engineering LLCAllentown Engineering

 

[Yongle]

With list in column A and NOTHING else in the sheet

1. Select Column A
2. Data Tab \ TextToColumns \Delimited \Next \mark "space" as delimiter \ Finish
3. Move everything in column B to below last entry in column A, same for column C etc until everything is in column A
4. Select column A \ Data Tab \ Sort \ Column A (gets rid of the blanks)
5. Formula in B1 copy down to end of data
=COUNTIF(A:A,A1)
6. Select Columns A & B (7, 8 & 9 are with columns A & B selected)
7. Copy and Paste Special Values (to remove formula)
8. Data Tab \ Remove Duplicates (A & B should be checked) \ OK
9. Data Tab \ Sort \ sort on column B

Now you have a list of all words sorted based on the number of occurrences

 

[Yongle]

Do you want to highlight company names containing specific "Keywords"?
Do you want a list of company names containing specific "keywords"?

 

[Fluff]

Another option

Sub MyCount()
   Dim Ary As Variant, v As Variant
   Dim i As Long, j As Long
   
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).value2
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         v = Split(Ary(i, 1))
         For j = 0 To UBound(v)
            .Item(v(j)) = .Item(v(j)) + 1
         Next j
      Next i
      Range("B1").Resize(.Count).Value = Application.Transpose(.keys)
      Range("C1").Resize(.Count).Value = Application.Transpose(.items)
   End With
End Sub

 

[Welsh Mark3]

The next thing I am trying to do. I know have a list of 100 Key words, Is there a matching function that tells me which of the names would contain the targeted key words?

 

[Yongle]

Can you be specific

what excatly is the desired RESULT?

 

[Welsh Mark3]

Can you be specific

what excatly is the desired RESULT?

So I have 100 Keywords such as supply, Manufacturing etc

The company names would be something like

Mark Edwards SUPPLY
Jones Manufacturing

The desired result would be to indicate if the company name contains any of the keywords, Just a simply Yes or No would work.

 

[Yongle]

This simply puts No/Yes in the cell if any of the key words is found in the string in referenced cell
Yes = at least one "complete word" match

With company name in A2, put this formula in B2 etc
=DoesNameContainAnyKeyword(A2)

Amend sheet name keywords to the one containing your keywords (assumes the words are listed separately in column A starting in row 2)

Place VBA in a standard module

Option Explicit

Function DoesNameContainAnyKeyword(CompanyName As String)
    Dim Keywords As Variant, keyword As Variant, Companies As Variant
    Dim wsKey As Worksheet, isFound As String, WordNo As Integer
    Set wsKey = Sheets("[COLOR=#ff0000]Keywords[/COLOR]")
    
'create 2 arrays
    CompanyName = CompanyName
    Keywords = wsKey.Range("A2", wsKey.Range("A" & Rows.Count).End(xlUp))
    Companies = Split(CompanyName, " ")
    
'test for a match
    isFound = "No"
    For WordNo = 0 To UBound(Companies)
        For Each keyword In Keywords
            If LCase(keyword) = LCase(Companies(WordNo)) Then
                isFound = "Yes"
                Exit For
            End If
        Next keyword
        If isFound = "Yes" Then Exit For
    Next WordNo

'return value
    DoesNameContainAnyKeyword = isFound
End Function

 

[Yongle]

OR
more flexible...

with company name in A2 and keywords separate cells in column A in sheet Keywords, this formula
=matchme(A2,Keywords!A:A)

Place VBA in a standard module

Function MatchMe(CompanyName As String, aRange As Range)
    Dim Companies As Variant, WordNo As Integer, isFound As String, a As Integer
    Companies = Split(CompanyName, " ")
    isFound = "No"
'test for match
    For WordNo = 0 To UBound(Companies)
        a = 0
        On Error Resume Next
        a = WorksheetFunction.Match(Companies(WordNo), aRange, 0)
        On Error GoTo 0
        If a > 0 Then
            isFound = "Yes"
            Exit For
        End If
    Next WordNo
'return value
    MatchMe = isFound
End Function

 

[Yongle]

You can delete this totally useless line in code in post#8

   CompanyName = CompanyName