lotto numbers

[marek]

I have a Draw Number in column (A), Draw Dates in (B), Draw Numbers
which are in random order in(C:H), and the current Draw Numbers in
(K)
I am using two formulas, which work perfect: {=SUMPRODUCT(--(MMULT(--
ISNUMBER(MATCH(C2:H2000,K22,0)),TRANSPOSE(COLUMN(C2:H2000)^0))=5))}
- that one shows me how many sets of 5 numbers of given combination
were drawn before
and the second formula: Conditional Formatting =AND(C2<>"",OR(C2=$K
$2:$P$2)), which highlights all the winning numbers.
-Question : Because of the very long list of the previous draws
( about 2000) how could I change existing formulas so I have a list of
the dates of instances when set of 5 numbers were drawn? At the moment
it tels me that set of 5 given numbers were drawn for example twice
before. But I have to scroll down through 2000 records to find 5
highlighted numbers.


A B C D E F
G H K L M N O P

220 6/01/08 3 4 19 21
25 32 2 10 16 24 38 44
221 6/08/08 24 25 38 8
10 7
222 6/15/08 12 23 28 38
22 15
223 6/22/08 37 16 45 14
38 10



Your help is appreciated. Thank you.

 

[Domenic]

Assuming that R2 contains the first formula, try...

S2, confirmed with CONTROL+SHIFT+ENTER, and copied down...

=IF(ROWS(S$2:S2)<=$R$2,SMALL(IF(MMULT(ISNUMBER(MATCH($C$2:$H$2000,$K$2:$P
$2,0))+0,TRANSPOSE(COLUMN($C$2:$H$2000)^0))=5,$B$2:$B$2000),ROWS(S$2:S2))
,"")

I noticed that there are 6 current draw numbers, yet you're trying to
match only 5. Is this intentional?

Hope this helps!

 

[marek]

Assuming that R2 contains the first formula, try...

S2, confirmed with CONTROL+SHIFT+ENTER, and copied down...

=IF(ROWS(S$2:S2)<=$R$2,SMALL(IF(MMULT(ISNUMBER(MATCH($C$2:$H$2000,$K$2:$P
$2,0))+0,TRANSPOSE(COLUMN($C$2:$H$2000)^0))=5,$B$2:$B$2000),ROWS(S$2:S2))
,"")

I noticed that there are 6 current drawnumbers, yet you're trying to
match only 5.  Is this intentional?

Hope this helps!









- Show quoted text -

Thank you very much Domenic, your formula works but only partially. It
shows me only 1 instance of 5 winning numbers, i.e. it's date, the
earliest one. For example, if there were 3 instances of 5 winning
numbers, my formula in R2 shows me number 3, but yours lists only 1
date, the earliest one.
In regards of matching 5 only numbers, you are right, it is
intentionally - for clarity. Later on I'm going to do the same with 4
and 6 winning numbers. I want to know when and how many times
previously, at given combination of numbers, there were instances of
4, 5 and 6 winning numbers.
Any idea, how to change Domenic's formula, so it lists all dates of 5
winning numbers not the first one only.
Thank you.