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