laly wrote..
I understand this formula search if all 11 numbers repeat more that
once ,but i need to know only 7 numbers or 6 numbers not 11 .....
Let say i have in A1=2 B1=5 C1=6 .................K1=11
A1=2 repeat in B18=2 and C1=6 repeat in D18=4
Sorry, I misunderstood your question.
There's no simple way to determine which combination of 6 or 7 numbers
in 1500 samples of 11 numbers is the most frequently appearing.
Certainly it's impractical to do this in any spreadsheet.
Just considering 6 number combinations, there are 462 combinations of 6
numbers in each of the 11 number samples. If there were 1500 such
samples, you'd need to check all 693,000 combinations. That's a
practical impossibility in any spreadsheet.
One VBA-based approach requires using the Dictionary object, which
requires adding a reference to the Scripting Runtime.
Sub foo()
Dim d As New Dictionary, v As Variant
Dim j As String, k As String, m As Long, n As Long
Dim i1 As Long, i2 As Long, i3 As Long
Dim i4 As Long, i5 As Long, i6 As Long
On Error GoTo CleanUp
v = Range("samples").Value2
For n = 1 To UBound(v, 1)
Application.StatusBar = CStr(n)
For i1 = 1 To 6
For i2 = i1 + 1 To 7
For i3 = i2 + 1 To 8
For i4 = i3 + 1 To 9
For i5 = i4 + 1 To 10
For i6 = i5 + 1 To 11
j = v(n, i1) & " " & v(n, i2) & " " & v(n, i3) & " " _
& v(n, i4) & " " & v(n, i5) & " " & v(n, i6)
If d.Exists(j) Then
d.Item(j) = d.Item(j) + 1
If d.Item(j) > m Then
m = d.Item(j)
k = j
End If
Else
d.Add Key:=j, Item:=1
End If
Next i6
Next i5
Next i4
Next i3
Next i2
Next i1
Next n
MsgBox Prompt:=k, Title:="Most frequent 6-tuple [" & CStr(m) & "
instances]"
CleanUp:
Application.StatusBar = False
End Sub
