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