frequency of occurance of all words in 2-D array
 

I have 2D array (size 100 x 50,000) of single-word text values (all single
words).

First, I need to determine all unique words contained in this 2-D array.
Then I need to count the number of times each word is found in the 2-D array.
--
Richard

frequency of occurance of all words in 2-D array
 

Richard,
Try this:

Sub UniqueEntry()

Dim i As Long
Dim arr
Dim coll As Collection
Dim counts() As Long


Set coll = New Collection

Set rngValues = Range("a1:d40") ' <=== your range

arr = rngValues

For j = 1 To Ubound(arr,2)
For i = 1 To UBound(arr,1)
On Error Resume Next
coll.Add arr(i, j), CStr(arr(i, j))
Next
Next j

ReDim counts(coll.Count)
For i = 1 To coll.Count
counts(i) = Application.CountIf(rngValues, coll(i)) '<== counts for
each entry
Next i

End Sub

HTH

"Richard" wrote:

I have 2D array (size 100 x 50,000) of single-word text values (all single
words).

First, I need to determine all unique words contained in this 2-D array.
Then I need to count the number of times each word is found in the 2-D array.
--
Richard

frequency of occurance of all words in 2-D array
 

Toppers,
Worked perfectly.
Some of the commands are over my head, but I will search web for specifics
on the commands I didn't understand.
Thanks so much.
--
Richard


"Toppers" wrote:

Richard,
Try this:

Sub UniqueEntry()

Dim i As Long
Dim arr
Dim coll As Collection
Dim counts() As Long


Set coll = New Collection

Set rngValues = Range("a1:d40") ' <=== your range

arr = rngValues

For j = 1 To Ubound(arr,2)
For i = 1 To UBound(arr,1)
On Error Resume Next
coll.Add arr(i, j), CStr(arr(i, j))
Next
Next j

ReDim counts(coll.Count)
For i = 1 To coll.Count
counts(i) = Application.CountIf(rngValues, coll(i)) '<== counts for
each entry
Next i

End Sub

HTH

"Richard" wrote:

I have 2D array (size 100 x 50,000) of single-word text values (all single
words).

First, I need to determine all unique words contained in this 2-D array.
Then I need to count the number of times each word is found in the 2-D array.
--
Richard