zoom38
11-14-2016, 10:11 AM
Hello I am trying to create a sub that will choose the value (strings or numbers) from a set or range of values that occur most often (mode) and then the next value that occurs most often until there are values left that only occur once. Once that is done I would like to randomly pick a value from the remaining values that have not already been chosen.
I have found the following multiple mode function while searching the net and like it very much. However I am trying to modify it so that if there are multiple modes, the values will be in different columns instead of the same cell but I can't figure out how to separate them. I tried changing it to a sub but was unsuccessful to get it to work correctly.
Function moder(x As Range)
Dim modes As New Collection
For Each ce In x
If WorksheetFunction.CountIf(x, ce) = _
WorksheetFunction.CountIf(x, WorksheetFunction.Mode(x)) Then
On Error Resume Next
modes.Add Item:=ce, Key:=Str(ce)
End If
Next
For i = 1 To modes.count
moder = moder & "," & modes(i)
Next i
moder = WorksheetFunction.Substitute(moder, ",", "", 1)
End Function
Any assistance would be appreciated. I'm using Excel 2007.
Thanks
Gary
I have found the following multiple mode function while searching the net and like it very much. However I am trying to modify it so that if there are multiple modes, the values will be in different columns instead of the same cell but I can't figure out how to separate them. I tried changing it to a sub but was unsuccessful to get it to work correctly.
Function moder(x As Range)
Dim modes As New Collection
For Each ce In x
If WorksheetFunction.CountIf(x, ce) = _
WorksheetFunction.CountIf(x, WorksheetFunction.Mode(x)) Then
On Error Resume Next
modes.Add Item:=ce, Key:=Str(ce)
End If
Next
For i = 1 To modes.count
moder = moder & "," & modes(i)
Next i
moder = WorksheetFunction.Substitute(moder, ",", "", 1)
End Function
Any assistance would be appreciated. I'm using Excel 2007.
Thanks
Gary