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/