Countif UDF???

[hjopertham]

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

 

[Bob Phillips]

Hi James,

Here is a shot

Function RandResult(rng1 As Range, rng2 As Range, criteria)
Dim sFormula As String
Dim i As Long
Dim result As Boolean
For i = 1 To rng2.Rows.Count
sFormula = "SUM(COUNTIF(" & rng2.Rows(i).Address & "," &
rng1.Address & "))" & criteria
result = Evaluate(sFormula)
If Not result Then
RandResult = "bad"
Exit Function
End If
Next i
RandResult = "useful"
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Leo Heuser]

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),"Useful","Bad")

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

Copy K1 down.

 

[Alan Beban]

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),"Useful","Bad")

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

Copy K1 down.

Leo,
What does A1:J1superscript0 mean?

Alan Beban

 

[Leo Heuser]

Leo,
What does A1:J1superscript0 mean?

Alan Beban

Hi Alan

One way of getting a 1 x 10 array of 1's (A1^0 = 1, B1^0 = 1 etc)

COUNTIF(OFFSET($L$1:$U$1,ROW($L$1:$U$5000)-ROW($L$1),0,1),A1:J1)

Returns a 5000 x 10 array

To get MMULT, the second argument has to be a 10 *row* array, which is, why
I do a TRANSPOSE(A1:J1^0) to get the 10 x 1 array.

LeoH

 

[Tom Ogilvy]

{1;1;1;1;1;1;1;1;1;1}

--
Regards,
Tom Ogilvy

Leo,
What does A1:J1superscript0 mean?

Alan Beban

 

[Alan Beban]

{1;1;1;1;1;1;1;1;1;1}

Thanks.

I take it the semicolons indicate a vertical array of 1's; so
TRANSPOSE(a1:j1superscript0) is a horizontal array of 1's? Unless I
misunderstand, Leo Heuser indicated in his post in this thread that it
is a vertical array of 1's; that the second argument to MMULT has to be
a 10row x 1column array.

What am I missing?

Thanks again,
Alan Beban

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;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.

 

[Tom Ogilvy]

That was the result of doing F9 on this much of the formula:

TRANSPOSE(A1:J1^0)

so it is after the transpose

 

[Alan Beban]

That was the result of doing F9 on this much of the formula:

TRANSPOSE(A1:J1^0)

so it is after the transpose

Thanks again.

Alan Beban

 

[Leo Heuser]

Thanks for the pointer, Tom.

--
Regards
LeoH

http://support.microsoft.com/default.aspx?scid=kb;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.

 

[hjopertham]

Dearest Bob Phillips,

My deepest gratitude for the UDF you so kindly constructed. I tested it
extensively, and its performance was outstanding.

Regards Jamie.


Hello VBA Experts,

I probably should start another thread but I'll give it a shot anyway.
I have 3 very minor programming questions.

1) With reference to the type of UDF that tests and loops through a
large number of rows in a designated range, such as the UDF that Bob
Phillips so kindly provided. If I construct a formula that contains up
to four levels of nested functions, including this type of UDF. Will
formula results always be reliable; what I mean is, is this type of
superformula within Excel's calculation capabilities via the 'formula
bar'? Results seem ok in testing, I just wanted to confirm there
shouldn't be any breakdown in calculation?

2) Can the following 2 terms be used in the construction of a macro?
sFormula = ".....
result = Evaluate(sFormula)

3) I need help with some coding. I've constructed some basic formatting
macros. What VBA code can I use so that my instructions will be applied
to ALL sheets in the workbook without requiring worksheet names etc. A
template of my two types of macro's follows. Would it be possible to
add the additional coding to help me achieve my aims?

Sub macroYY
Columns("A:A").Select

'any formatting code, doesn't matter for my question
End With
End Sub

_______
Sub macroRR
Columns("A:A").Select

'any formatting code, doesn't matter for my question
Range("B1").Select
End Sub

Regards
James

 

[Bob Phillips]

Dearest Bob Phillips,

My deepest gratitude for the UDF you so kindly constructed. I tested it
extensively, and its performance was outstanding.

Thank-you, my pleasure.

1) With reference to the type of UDF that tests and loops through a
large number of rows in a designated range, such as the UDF that Bob
Phillips so kindly provided. If I construct a formula that contains up
to four levels of nested functions, including this type of UDF. Will
formula results always be reliable; what I mean is, is this type of
superformula within Excel's calculation capabilities via the 'formula
bar'? Results seem ok in testing, I just wanted to confirm there
shouldn't be any breakdown in calculation?

Reliability is not likely to be the problem as long as the VBA code
functions correctly. The biggest problem is likely to be performance.
Nesting functions incur a performance hit (may be unavoidable, but it is
there), but the UDF is even worse. ALl UDFs incur a performance hit, in
calling the UDF, and in running VBA code.

2) Can the following 2 terms be used in the construction of a macro?
sFormula = ".....
result = Evaluate(sFormula)

Not sure that I understand this, isn't this what I demonstrated in the UDF?

3) I need help with some coding. I've constructed some basic formatting
macros. What VBA code can I use so that my instructions will be applied
to ALL sheets in the workbook without requiring worksheet names etc. A
template of my two types of macro's follows. Would it be possible to
add the additional coding to help me achieve my aims?

For Each ws In Activeworkbook.Worksheets
YY ws
RR ws
Next ws



Sub macroYY(sh as worksheet)
with sh
.Columns("A:A").Select

'any formatting code, doesn't matter for my question
End With
End Sub

_______
Sub macroRR(sh as worksheet)
with sh
.Columns("A:A").Select

'any formatting code, doesn't matter for my question
Range("B1").Select
End Sub

 

[hjopertham]

Thanks Bob for your help & tips.

Not sure that I understand this, isn't this what I demonstrated in

the UDF?


With reference to my second follow-up question. What I meant was I've
previously seen those two particular VBA instructions in UDF codes. But
I've never seen it in macro instructions. Is it ok to use those two
instructions in a macro? I want to try constructing some code.

(Before I posted my countif UDF request, I even laughably attempted to
construct the UDF myself using the template "sFormula = ".......")

Regards James.

 

[Bob Phillips]

Hi James,

I see your point now.

A UDF is a macro, it is just a particular type of macro that can be used in
a worksheet formula. So the answer to your question is an emphatic yes.

--

HTH

RP
(remove nothere from the email address if mailing direct)