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/