http://support.microsoft.com/default...b;en-us;166342
Description of the limitations for working with arrays in Excel 2000, Excel
2002, and Excel 2003

has some information on limits. For mmult, see towards the bottom of the
article.

--
Regards,
Tom Ogilvy


"Leo Heuser" wrote in message
...
Hi James

FWIW here is a array formula solution, but I believe, that 30000 rows
are too many for this kind of formula.

In K1:

=IF(AND(MMULT(COUNTIF(OFFSET($L$1:$U$1,ROW($L$1:$U $5000)-
ROW($L$1),0,1),A1:J1),TRANSPOSE(A1:J1^0))<7),"Usef ul","Bad")

The formula must be entered with <Shift<Ctrl<Enter, also if edited
later.

Copy K1 down.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

skrev i en meddelelse
oups.com...
Hello Experts,

I am trying to look for a UDF solution.

My worksheet setup: Range A1:J30000 is my randomly generated number
sequence (numbers 1-99). And L1:U5000 is my database of archived
sequences.

I have simplified the following worksheet setup so it's easier to
explain and hopefully easier to construct code for:

A B C D E F G H I J K_______L M N O P Q R S T U _______V
1 2 3 4 5 6 7 8 9 10 bad____1 2 3 4 5 6 30 31 32 33____TRUE
____________________________1 2 3 4 5 6 30 31 32 33____TRUE
____________________________1 2 3 4 5 6 7 31 32 33_____FALSE
____________________________1 2 3 4 5 6 30 31 32 33____TRUE


If I am testing the similarity of the randomly generated sequence A1:J1
to 4 archive sequences L1:U1 L2:U2 L3:U3 & L4:U4. I use the array
formulas.

Cell K1 =IF(OR(FALSE=V1:V4),"bad","useful")

Cell V1 =SUM(COUNTIF(L1:U1,$A$1:$J$1))<7
Cell V2 =SUM(COUNTIF(L2:U2,$A$1:$J$1))<7
Cell V3 =SUM(COUNTIF(L3:U3,$A$1:$J$1))<7
Cell V4 =SUM(COUNTIF(L4:U4,$A$1:$J$1))<7


I am trying to avoid the 2 step formula process. I would like the UDF
to automatically test each row in the range L1:U4 and return a
conclusion of it's findings. In others words to set the above formulas
up as one user-defined function in VBA and simply pass along the
necessary variables for VBA to handle.

Based on the above worksheet example, all I would need to enter is the
UDF in Cell K1

=UDFName(L1:U4,A1:J1,"<7") returns the result "bad"

PLEASE NOTE: 1) The UDF must be able to test other range dimensions.
2)The test criteria must be variable, hence I can enter it in the
function.


I have a large amounts of data on my worksheet, and I need to check
30,000 combinations and additional databases. Can anybody help me to
find a UDF solution? Any help you could give me would be gratefully be
appreciated.


Regards

James