0

I used following function to gather most frequent member in array column:

Function MosFreqinsimplearr(ByRef arrin As Variant, colindx As Integer) As Variant
Dim i As Integer

Set dic = CreateObject("scripting.dictionary")
On Error Resume Next

xMax = 0
xOutValue = ""
For i = 1 To UBound(arrin)
    xValue = arrin(i, colindx)
    If xValue <> "" Then
        dic(xValue) = dic(xValue) + 1
        xCount = dic(xValue)
        If xCount > xMax Then
            xMax = xCount
            xOutValue = xValue
        End If
    End If
Next i

MosFreqinsimplearr = xOutValue
Set dic = Nothing
End Function

1- I need procedure for returning minimum-repeated or less frequent member(s). it seems that same procedure could not be used for getting such result, as i tried:

Dim dic As Object
Dim j As Integer
Dim xMin As Integer
Dim xOutValue As String
Dim xValue As String
Dim xCount As Integer
Dim ar(1 To 11) As Variant

ar(1) = "banana"
ar(2) = "banana"
ar(3) = "banana"
ar(4) = "apple"
ar(5) = "apple"
ar(6) = "banana"
ar(7) = "cucumber"
ar(8) = "cucumber"
ar(9) = "cucumber"
ar(10) = "apple"
ar(11) = "cucumber"

Set dic = CreateObject("scripting.dictionary")
'On Error Resume Next
xMin = 0
xOutValue = ""
For j = 1 To UBound(ar)
    xValue = ar(j)
    If xValue <> "" Then
        dic(xValue) = dic(xValue) + 1
        xCount = dic(xValue)
        If xCount <= xMin Then
            xMin = xCount
            xOutValue = xValue
            Else: xOutValue = xValue
        End If

    End If
Next j
MsgBox "less repeated value is:" & vbTab & xOutValue
Set dic = Nothing

2-what is the code for counting number of each unique value :

banana=4
cucumber=4
apple=3

Regards;

  • Look into [sorting collections/dictionaries](http://www.cpearson.com/excel/CollectionsAndDictionaries.htm)? – QHarr Dec 27 '17 at 15:18

1 Answers1

0

You need to iterate over the Keys of the Dictionary and compare the Values.

Dim key As Variant, minKey As Variant

For Each key In dic
    If IsEmpty(minKey) Then
        minKey = key
    Else
        If dic(key) < dic(minKey) Then minKey = key
    End If
Next