Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number to words literal | Excel Worksheet Functions | |||
Transpose words and numbers into array of different proportions | Excel Discussion (Misc queries) | |||
Question to Bob Phillips (or whoever...) | Excel Worksheet Functions | |||
Frequency Array and Bin Array | Excel Worksheet Functions | |||
frequency for each occurance | Excel Worksheet Functions |