This user defined function creates an unique list of words and their frequency in selected range.

User defined function:

=FreqWords(cell_range, position)+ Ctrl + Shift + Enter

Udf in cell E3:E30:

=FreqWords(B2:C11, 1)+ Ctrl + Shift + Enter

How to create an array formula

  1. Select cell range E3:E30.
  2. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  3. Press and hold Ctrl + Shift.
  4. Press Enter once.
  5. Release all keys.

Udf in cell F3:F30

=FreqWords(B2:C11, 2) + Ctrl + Shift + Enter

VBA

Function FreqWords(tbl_array As Range, pos As Integer) As Variant()
Dim cell As Variant, wrds As Variant, i As Integer
Dim a As Integer, j As Integer
Dim tmp() As String, nr() As Integer
ReDim tmp(0), nr(0)
nr(0) = 1
For Each cell In tbl_array
  wrds = Split(cell)
  For i = 0 To UBound(wrds)
    For j = 0 To UBound(tmp)
      If wrds(i) = tmp(j) Then
        nr(j) = nr(j) + 1
        a = 1
        Exit For
      End If
    Next j
    If a <> 1 Then
      tmp(UBound(tmp)) = wrds(i)
      ReDim Preserve tmp(UBound(tmp) + 1)
      ReDim Preserve nr(UBound(tmp))
      nr(UBound(tmp)) = 1
    End If
    a = 0
  Next i
Next cell
If pos = 1 Then
  ReDim Preserve tmp(UBound(tmp) - 1)
  FreqWords = Application.Transpose(tmp)
Else
  ReDim Preserve nr(UBound(nr) - 1)
  FreqWords = Application.Transpose(nr)
End If
End Function

How to use user defined function in excel

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste vba code
  4. Exit visual basic editor

Download excel example file

Frequent words.xls
(Excel 97-2003  Workbook *.xls)

Related posts:

Excel udf: Filter emails from an excel range

User defined function to split words in a cell range into a cell each in excel

Select numbers in each permutation

Excel udf: Combine cell ranges into a single range while eliminating blanks

Copy excel table filter criteria (vba)