I have about 3000 combinations of 5 or 6 or 7 or 10 numbers

I also have 700 rows in 5 columns that are fill with combinations of
numbers
A B C D E
---------------------
eg. 1 2 5 8 10
5 20 40 41 42
12 22 35 39 40

The point is to find out how many matching numbers each of the 300
combos has with the 700 combos.

I' ve made a formula that reads one by one all the above 300
combinations
and finds if any combination has numbers that are included in each o
the 5 numbers combinations.
eg.

if the first combination of the 3000 is:
A B C D E F G
--------------
1 2 3 4 5 6 10

and the first 4 of the 700 combinations a
A B C D E
-------------
1 10 12 40 41
1 5 20 22 25
2 6 10 20 22
12 20 21 25 30

I want to find out how many numbers of the 1st combo are matching the
combos

The result of the formula will be
A B C D E
--------------------
1st row 1 1 0 0 0
2nd row 1 1 0 1 0
3nd row 1 1 1 0 0
4nd row 0 0 0 0 0

thats because if there are any matching numbers it returns the '1
otherwise the '0'
At last it sums the '1' and it writes it in column.
So i get
A
----------
1st row 2
2nd row 3
3nd row 3
4nd row 0

After all these i use a countif() to count how many 0, 1 , 2 , 3 ,
and 5 are returned from the
formula for each combination

So i get for the first 4 combinations of the 700 :

A B C D E F G I J K L M
-------------------------------
0 1 2 3 4 5
---------------- ------------
1 2 3 4 5 6 10 1 0 1 2 0 0

thats the whole point.
Next i continue with the 2nd combination, and so on.

But when i do it using the formula it was running about 60 minutes. I
there any otherway eg. arrays to give the result without waiting s
long??

Thanks in advance
Stathi

--
Message posted from http://www.ExcelForum.com

stakar,
You need to save these 2.1 million results, or just trying to find the best
combinations ?

NickHK

"stakar " wrote in message
...
I have about 3000 combinations of 5 or 6 or 7 or 10 numbers

I also have 700 rows in 5 columns that are fill with combinations of 5
numbers
A B C D E
---------------------
eg. 1 2 5 8 10
5 20 40 41 42
12 22 35 39 40

The point is to find out how many matching numbers each of the 3000
combos has with the 700 combos.

I' ve made a formula that reads one by one all the above 3000
combinations
and finds if any combination has numbers that are included in each of
the 5 numbers combinations.
eg.

if the first combination of the 3000 is:
A B C D E F G
--------------
1 2 3 4 5 6 10

and the first 4 of the 700 combinations a
A B C D E
-------------
1 10 12 40 41
1 5 20 22 25
2 6 10 20 22
12 20 21 25 30

I want to find out how many numbers of the 1st combo are matching the 4
combos

The result of the formula will be
A B C D E
--------------------
1st row 1 1 0 0 0
2nd row 1 1 0 1 0
3nd row 1 1 1 0 0
4nd row 0 0 0 0 0

thats because if there are any matching numbers it returns the '1'
otherwise the '0'
At last it sums the '1' and it writes it in column.
So i get
A
----------
1st row 2
2nd row 3
3nd row 3
4nd row 0

After all these i use a countif() to count how many 0, 1 , 2 , 3 , 4
and 5 are returned from the
formula for each combination

So i get for the first 4 combinations of the 700 :

A B C D E F G I J K L M
-------------------------------
0 1 2 3 4 5
---------------- ------------
1 2 3 4 5 6 10 1 0 1 2 0 0

thats the whole point.
Next i continue with the 2nd combination, and so on.

But when i do it using the formula it was running about 60 minutes. Is
there any otherway eg. arrays to give the result without waiting so
long??

Thanks in advance
Stathis


---
Message posted from http://www.ExcelForum.com/

NickHK wrote:
*stakar,
You need to save these 2.1 million results, or just trying to fin
the best
combinations ?

NickHK

"stakar " wrote in message
...
I have about 3000 combinations of 5 or 6 or 7 or 10 numbers

I also have 700 rows in 5 columns that are fill with combination
of 5
numbers
A B C D E
---------------------
eg. 1 2 5 8 10
5 20 40 41 42
12 22 35 39 40

The point is to find out how many matching numbers each of th
3000
combos has with the 700 combos.

I' ve made a formula that reads one by one all the above 3000
combinations
and finds if any combination has numbers that are included in eac
of
the 5 numbers combinations.
eg.

if the first combination of the 3000 is:
A B C D E F G
--------------
1 2 3 4 5 6 10

and the first 4 of the 700 combinations a
A B C D E
-------------
1 10 12 40 41
1 5 20 22 25
2 6 10 20 22
12 20 21 25 30

I want to find out how many numbers of the 1st combo are matchin
the 4
combos

The result of the formula will be
A B C D E
--------------------
1st row 1 1 0 0 0
2nd row 1 1 0 1 0
3nd row 1 1 1 0 0
4nd row 0 0 0 0 0

thats because if there are any matching numbers it returns the '1'
otherwise the '0'
At last it sums the '1' and it writes it in column.
So i get
A
----------
1st row 2
2nd row 3
3nd row 3
4nd row 0

After all these i use a countif() to count how many 0, 1 , 2 , 3
4
and 5 are returned from the
formula for each combination

So i get for the first 4 combinations of the 700 :

A B C D E F G I J K L M
-------------------------------
0 1 2 3 4 5
---------------- ------------
1 2 3 4 5 6 10 1 0 1 2 0 0

thats the whole point.
Next i continue with the 2nd combination, and so on.

But when i do it using the formula it was running about 60 minutes
Is
there any otherway eg. arrays to give the result without waitin
so
long??

Thanks in advance
Stathis


---
Message posted from http://www.ExcelForum.com/
*

Just trying to find out the best combination

--
Message posted from http://www.ExcelForum.com

there any otherway eg. arrays to give the result without waiting so
long??


Best way to answer that would be for you to write the code that gets the
results you want and run it - compare the time to the times you are getting
now. There is nothing particularly magic about arrays - I expect excel is
using arrays internally in compiled code to perform its calculations.
However, excel has to attack the problem in a general fashion that may
include repetitive work that can be avoided in a custom algorithm. Doing
massive comparisons is going to take time. Writing code takes time.

--
Regards,
Tom Ogilvy

"stakar " wrote in message
...
NickHK wrote:
*stakar,
You need to save these 2.1 million results, or just trying to find
the best
combinations ?

NickHK

"stakar " wrote in message
...
I have about 3000 combinations of 5 or 6 or 7 or 10 numbers

I also have 700 rows in 5 columns that are fill with combinations
of 5
numbers
A B C D E
---------------------
eg. 1 2 5 8 10
5 20 40 41 42
12 22 35 39 40

The point is to find out how many matching numbers each of the
3000
combos has with the 700 combos.

I' ve made a formula that reads one by one all the above 3000
combinations
and finds if any combination has numbers that are included in each
of
the 5 numbers combinations.
eg.

if the first combination of the 3000 is:
A B C D E F G
--------------
1 2 3 4 5 6 10

and the first 4 of the 700 combinations a
A B C D E
-------------
1 10 12 40 41
1 5 20 22 25
2 6 10 20 22
12 20 21 25 30

I want to find out how many numbers of the 1st combo are matching
the 4
combos

The result of the formula will be
A B C D E
--------------------
1st row 1 1 0 0 0
2nd row 1 1 0 1 0
3nd row 1 1 1 0 0
4nd row 0 0 0 0 0

thats because if there are any matching numbers it returns the '1'
otherwise the '0'
At last it sums the '1' and it writes it in column.
So i get
A
----------
1st row 2
2nd row 3
3nd row 3
4nd row 0

After all these i use a countif() to count how many 0, 1 , 2 , 3 ,
4
and 5 are returned from the
formula for each combination

So i get for the first 4 combinations of the 700 :

A B C D E F G I J K L M
-------------------------------
0 1 2 3 4 5
---------------- ------------
1 2 3 4 5 6 10 1 0 1 2 0 0

thats the whole point.
Next i continue with the 2nd combination, and so on.

But when i do it using the formula it was running about 60 minutes.
Is
there any otherway eg. arrays to give the result without waiting
so
long??

Thanks in advance
Stathis


---
Message posted from http://www.ExcelForum.com/
*

Just trying to find out the best combinations


---
Message posted from http://www.ExcelForum.com/

stakar,
I've got something that can get a total of ~2.7 million tests into an array
in 1.6 minutes.
If you avoid the Excel side and do all in memory, it would faster I imagine.

Post your email (hidden from the spammers) and I'll send you a worksheet.
Hope it helps, not if it's better, but seems faster .

NickHK

"stakar " wrote in message
...
NickHK wrote:
*stakar,
You need to save these 2.1 million results, or just trying to find
the best
combinations ?

NickHK

"stakar " wrote in message
...
I have about 3000 combinations of 5 or 6 or 7 or 10 numbers

I also have 700 rows in 5 columns that are fill with combinations
of 5
numbers
A B C D E
---------------------
eg. 1 2 5 8 10
5 20 40 41 42
12 22 35 39 40

The point is to find out how many matching numbers each of the
3000
combos has with the 700 combos.

I' ve made a formula that reads one by one all the above 3000
combinations
and finds if any combination has numbers that are included in each
of
the 5 numbers combinations.
eg.

if the first combination of the 3000 is:
A B C D E F G
--------------
1 2 3 4 5 6 10

and the first 4 of the 700 combinations a
A B C D E
-------------
1 10 12 40 41
1 5 20 22 25
2 6 10 20 22
12 20 21 25 30

I want to find out how many numbers of the 1st combo are matching
the 4
combos

The result of the formula will be
A B C D E
--------------------
1st row 1 1 0 0 0
2nd row 1 1 0 1 0
3nd row 1 1 1 0 0
4nd row 0 0 0 0 0

thats because if there are any matching numbers it returns the '1'
otherwise the '0'
At last it sums the '1' and it writes it in column.
So i get
A
----------
1st row 2
2nd row 3
3nd row 3
4nd row 0

After all these i use a countif() to count how many 0, 1 , 2 , 3 ,
4
and 5 are returned from the
formula for each combination

So i get for the first 4 combinations of the 700 :

A B C D E F G I J K L M
-------------------------------
0 1 2 3 4 5
---------------- ------------
1 2 3 4 5 6 10 1 0 1 2 0 0

thats the whole point.
Next i continue with the 2nd combination, and so on.

But when i do it using the formula it was running about 60 minutes.
Is
there any otherway eg. arrays to give the result without waiting
so
long??

Thanks in advance
Stathis


---
Message posted from http://www.ExcelForum.com/
*

Just trying to find out the best combinations


---
Message posted from http://www.ExcelForum.com/

NickHK
how can i give you my email without seeing the others

--
Message posted from http://www.ExcelForum.com

I don't think that is what he meant. I think he meant to deny its use by
spammers

mr dot lottery at someplace dot com

would hide it from the spammers.

--
Regards,
Tom Ogilvy

"stakar " wrote in message
...
NickHK
how can i give you my email without seeing the others ?


---
Message posted from http://www.ExcelForum.com/

Thanks Tom for the replay

so, my email is

stathiskarathanasis at yahoo dot gr

Awaiting the fil

--
Message posted from http://www.ExcelForum.com