how to find top occurances of a string in an array??

[notageek7]

I've got an array of strings which have numerous instances of the same strings, and would like to find the top 3 strings and their associated count. I'm sure this has been done before so if you know of a quick and efficient way of accomplishing this I'd be very grateful. Thanks in advance for your time.

The array could contain for example an array of error codes

arr("MD04","MD03", "MD04","CP12","MD04","MD2",......")

So, would like to know "MD04" had 112 occurances and
"MD02" had 33 and so on....

 

[traingamer]

select top 3 Count([your error field])
from yourtable
group by [your error field]
order by Count([your error field]) DESC

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

As a starting point create the following function in a standard code module (ac2k or above):

Public Function getTopN(arr, top As Integer)
If Not IsArray(arr) Or top <= 0 Then Exit Function
Dim t As String, a, n As Integer, m As Integer, d, l
Set d = CreateObject("Scripting.Dictionary")
Set l = CreateObject("Scripting.Dictionary")
t = "," & Join(arr, ",,") & ","
For Each a In arr
  If Not d.Exists(a) Then
    n = (Len(t) - Len(Replace(t, "," & a & ",", ""))) / (2 + Len(a))
    d.Add a, n
    If l.Exists(n) Then l(n) = l(n) & "," & a Else l.Add n, a
    If n > m Then m = n
  End If
Next a
For n = 1 To top
  t = IIf(n = 1, "", t & vbCrLf) & m & " occurences of " & l(m)
  l.Remove (m): m = 0
  For Each a In l.Keys
    If a > m Then m = a
  Next a
  If m = 0 Then Exit For
Next n
getTopN = t
End Function

Example of use:
MsgBox getTopN(arr, 3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[notageek7]

Thank you both for your input. Traingamer the only problem with using sql is the error code field may have multiple entries per record:

key owner error codes ....
1 jeff MD04,MD03,CP12 ....
2 fred MD01 ....
3 sam MD03,MD11 ....


so not sure how to accomplish in sql???

 

[notageek7]

Your suggestions have always been good PHV so I'd like to try to understand what your getting at. Not sure why your doing the code in the beginning of the function you sent but the following code at the bottom of your function reminds me of something I've done before.

For n = 1 To top
t = IIf(n = 1, "", t & vbCrLf) & m & " occurences of " & l(m)
l.Remove (m): m = 0
For Each a In l.Keys
If a > m Then m = a
Next a
If m = 0 Then Exit For
Next n
getTopN = t

 

[PHV]

Ah, you don't have an array but a comma separated list ?

Public Function getTopX(lst, top As Integer)
If IsNull(lst) Or top <= 0 Then Exit Function
Dim t As String, a, n As Integer, m As Integer, d, l
Set d = CreateObject("Scripting.Dictionary")
Set l = CreateObject("Scripting.Dictionary")
t = "," & Replace(lst, ",", ",,") & ","
For Each a In Split(lst, ",")
  If Not d.Exists(a) Then
    n = (Len(t) - Len(Replace(t, "," & a & ",", ""))) / (2 + Len(a))
    d.Add a, n
    If l.Exists(n) Then l(n) = l(n) & "," & a Else l.Add n, a
    If n > m Then m = n
  End If
Next a
For n = 1 To top
  t = IIf(n = 1, "", t & "; ") & m & " x " & l(m)
  l.Remove (m): m = 0
  For Each a In l.Keys
    If a > m Then m = a
  Next a
  If m = 0 Then Exit For
Next n
getTopX = t
End Function

SELECT [key], [owner], getTopX([error codes], 3) AS Top3Errors

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[notageek7]

I apologize for not explaining myself better in the beginning. I'm creating a form that allows the user to see statistical info on the orders with errors in them. I've got most of it working now and am passing an array to a function that I want to retrieve the top 3 error codes and their count. It looks like everything is working I just need to add code to the bottom of the outer for loop which will arrange, and keep arranged, the error codes in the proper order.




For indexI = 0 To UBound(allErrCodeArr)
compString = allErrCodeArr(indexI)

For indexJ = 0 To UBound(allErrCodeArr)

If compString = strTopErrCode Or compString = strTopErrCode2 Or compString = strTopErrCode3 Then
GoTo Bypass
End If

If allErrCodeArr(indexJ) = compString Then
cntcompString = cntcompString + 1
End If
Next

If strTopErrCode = compString Or strTopErrCode = "" Then
strTopErrCode = compString
cntTopErrCode = cntTopErrCode + cntcompString
ElseIf strTopErrCode2 = compString Or strTopErrCode2 = "" Then
strTopErrCode2 = compString
cntTopErrCode2 = cntTopErrCode2 + cntcompString
ElseIf strTopErrCode3 = compString Or strTopErrCode3 = "" Then
strTopErrCode3 = compString
cntTopErrCode3 = cntTopErrCode3 + cntcompString
Else
tempstring = compString
cnttempstring = cntcompString
End If


cntcompString = 0

'Need to add code HERE to rearrange the top three codes and keep them in proper order and put the tempstring on one of their places if high count



Bypass:

Next

 

[PHV]

Have you tried getTopN(allErrCodeArr, 3) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244