# frequency of occurance of all words in 2-D array

1. ## 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  Register To Reply

2. ## RE: 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  Register To Reply

3. ## RE: 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  Register To Reply

4. ## RE: 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  Register To Reply

5. ## RE: 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  Register To Reply

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1