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:P)
I am using two formulas, which work perfect: {=SUMPRODUCT(--(MMULT(--
ISNUMBER(MATCH(C2:H2000,K2:P2,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.

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(MAT CH($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!

In article
,
marek wrote:

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:P)
I am using two formulas, which work perfect: {=SUMPRODUCT(--(MMULT(--
ISNUMBER(MATCH(C2:H2000,K2:P2,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.

On Jun 22, 12:45*am, Domenic wrote:
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(MAT CH($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!

In article
,



*marek wrote:
* *I have a Draw Number in column (A), Draw Dates in (B), DrawNumbers
which are in random order in(C:H), and the current DrawNumbersin
(K:P)
I am using two formulas, which work perfect: *{=SUMPRODUCT(--(MMULT(--
ISNUMBER(MATCH(C2:H2000,K2:P2,0)),TRANSPOSE(COLUMN (C2:H2000)^0))=5))}
- that one shows me how many sets of 5numbersof given combination
were drawn before
and the second formula: Conditional Formatting *=AND(C2<"",OR(C2=$K
$2:$P$2)), which highlights all the winningnumbers.
-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 5numberswere drawn? At the moment
it tels me that set of 5 givennumberswere drawn for example twice
before. But I have to *scroll down through 2000 records to find 5
highlightednumbers.

* * 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.- Hide quoted text -

- 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.