# How to find the most common numbers?



## alikirca20 (Apr 9, 2009)

I have 300 number groups end each group has 22 different numbers (from 1 to 80).

Can I find the most common 5 or more numbers in these groups by excel, macro or any program? 

for example, let us suppose that 4-15-23-36-45 are the most common numbers, these numbers are in 8 groups.

Is it possible, I added my file, Thanks,


----------



## Ent (Apr 11, 2009)

You want the most common numbers in all the 6600, or just per group?


----------



## Ent (Apr 11, 2009)

Starting with all the data in column A, make a macro in excel (A simple form with a button will do). Paste in the following script, and run it. It will make, in column B, a sorted list of numbers and in column C the corresponding count. If anything is wrong let me know.



Private Sub CommandButton1_Click()
Dim countlist(80) As Integer
Dim rank(80) As Integer
Dim temp As Integer
For Colomn = 1 To 6600
countlist(Sheet1.Cells(Colomn, 1).Value) = countlist(Sheet1.Cells(Colomn, 1).Value) + 1
Next Colomn

For R = 0 To 80
rank(R) = R
Next R

For rer = 0 To 80
For R = 0 To 79
If countlist(rank(R)) > countlist(rank(R + 1)) Then
temp = rank(R + 1)
rank(R + 1) = rank(R)
rank(R) = temp
End If
Next R
Next rer

For Colomn = 1 To 80
C = 81 - Colomn
Sheet1.Cells(C, 2).Value = rank(Colomn)
Sheet1.Cells(C, 3).Value = countlist(rank(Colomn))
Next Colomn
End Sub


----------



## Ent (Apr 11, 2009)

Set another cell to =Mode(A1:A6600)
record the answer (In another Program!)
Replace all of that answer with =RAND
repeat for the five highest values.

DO NOT SAVE!

This method won't tell you how many of each number appeared, but will give you the high values.


----------

