Hi,

I have a woksheet with 11 random numbers 1 to 35 in (A1-K1)
i like to know what combination of 7 numbers from 11 ...are the most
repeat in A1-A1500
I dont know the numbers ,I want to know which numbers (COMBINATION OF 7
)

Thanks


--
laly
------------------------------------------------------------------------
laly's Profile: http://www.excelforum.com/member.php...o&userid=27303
View this thread: http://www.excelforum.com/showthread...hreadid=471238

laly wrote...
I have a woksheet with 11 random numbers 1 to 35 in (A1-K1)
i like to know what combination of 7 numbers from 11 ...are the most
repeat in A1-A1500
....

Unclear.

It seems you have 11 numbers drawn at random without replacement from 1
to 35 in cells A1:K1. It seems you also have something in cells
A1-A1500. Is that a different cell A1? Or is the first random integer
in A1 also always the first number in the range A1:A1500?

What *exactly* is in each cell in A1:A1500? Individual numbers in the
range 1 to 35 or strings of 7 space-separated integers drawn without
replacement from 1 to 35?

http://tinyurl.com/8dfoq

Biff

"Harlan Grove" wrote in message
oups.com...
laly wrote...
I have a woksheet with 11 random numbers 1 to 35 in (A1-K1)
i like to know what combination of 7 numbers from 11 ...are the most
repeat in A1-A1500
...

Unclear.

It seems you have 11 numbers drawn at random without replacement from 1
to 35 in cells A1:K1. It seems you also have something in cells
A1-A1500. Is that a different cell A1? Or is the first random integer
in A1 also always the first number in the range A1:A1500?

What *exactly* is in each cell in A1:A1500? Individual numbers in the
range 1 to 35 or strings of 7 space-separated integers drawn without
replacement from 1 to 35?

11 random numbers 1 to 35 in (A1-K1)


1 3 7 8 13 23 27 29 30 33 34
6 8 9 12 15 20 21 22 24 25 28
1 2 17 18 19 22 25 26 28 31 35
8 9 13 14 15 18 19 22 27 30 31
5 8 11 14 17 18 20 23 24 26 30
9 12 19 24 25 26 28 29 30 33 35
3 4 7 8 13 14 23 24 26 27 33


--
laly
------------------------------------------------------------------------
laly's Profile: http://www.excelforum.com/member.php...o&userid=27303
View this thread: http://www.excelforum.com/showthread...hreadid=471238

laly wrote...
11 random numbers 1 to 35 in (A1-K1)

1 3 7 8 13 23 27 29 30 33 34
6 8 9 12 15 20 21 22 24 25 28
1 2 17 18 19 22 25 26 28 31 35
8 9 13 14 15 18 19 22 27 30 31
5 8 11 14 17 18 20 23 24 26 30
9 12 19 24 25 26 28 29 30 33 35
3 4 7 8 13 14 23 24 26 27 33

Use ancillary formulas in column L, e.g.,

L1:
=A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&"
"&K1

The tommost most frequently repeating combination would be given by the
array formula

=INDEX(A1:K1500,MODE(MATCH(L1:L1500,L1:L1500,0)),0 )

It not working ..... :(


--
laly
------------------------------------------------------------------------
laly's Profile: http://www.excelforum.com/member.php...o&userid=27303
View this thread: http://www.excelforum.com/showthread...hreadid=471238

laly wrote...
It not working ..... :(

If you mean it's returning #N/A, then you may not have any combination
with more than a single instance.

Hi

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


--
laly
------------------------------------------------------------------------
laly's Profile: http://www.excelforum.com/member.php...o&userid=27303
View this thread: http://www.excelforum.com/showthread...hreadid=471238

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