Countif UDF???

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

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)


<hjopertham@yahoo.co.uk> wrote in message
news:1116069911.577981.214930@z14g2000cwz.googlegroups.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
>

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.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

<hjopertham@yahoo.co.uk> skrev i en meddelelse
news:1116069911.577981.214930@z14g2000cwz.googlegroups.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
>

Leo Heuser wrote:
> 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

"Alan Beban" <unavailable@no.com> skrev i en meddelelse
news:%23m7amhVWFHA.2572@TK2MSFTNGP14.phx.gbl...
> Leo Heuser wrote:
>> 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

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

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

--
Regards,
Tom Ogilvy


"Alan Beban" <unavailable@no.com> wrote in message
news:%23m7amhVWFHA.2572@TK2MSFTNGP14.phx.gbl...
> Leo Heuser wrote:
> > 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

Tom Ogilvy wrote:
> {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

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" <leo.heuser@NOSPAMadslhome.dk> wrote in message
news:eLHk9RTWFHA.2540@tk2msftngp13.phx.gbl...
> 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.
>
>
> --
> Best Regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
> <hjopertham@yahoo.co.uk> skrev i en meddelelse
> news:1116069911.577981.214930@z14g2000cwz.googlegroups.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
> >
>
>

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

TRANSPOSE(A1:J1^0)

so it is after the transpose

--
Regards,
Tom Ogilvy


"Alan Beban" <unavailable@no.com> wrote in message
news:O2oQvLXWFHA.1224@TK2MSFTNGP10.phx.gbl...
> Tom Ogilvy wrote:
> > {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 wrote:
> 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

Thanks for the pointer, Tom.

--
Regards
LeoH


"Tom Ogilvy" <twogilvy@msn.com> skrev i en meddelelse
news:Oa57AQXWFHA.3840@tk2msftngp13.phx.gbl...
> 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" <leo.heuser@NOSPAMadslhome.dk> wrote in message
> news:eLHk9RTWFHA.2540@tk2msftngp13.phx.gbl...
>> 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.
>>
>>
>> --
>> Best Regards
>> Leo Heuser
>>
>> Followup to newsgroup only please.
>>
>> <hjopertham@yahoo.co.uk> skrev i en meddelelse
>> news:1116069911.577981.214930@z14g2000cwz.googlegroups.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
>> >
>>
>>
>
>

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 wrote:
> 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
>...........

<hjopertham@yahoo.co.uk> wrote in message
news:1116343681.418101.35710@f14g2000cwb.googlegroups.com...
> 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

Thanks Bob for your help & tips.

> > 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?


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.

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)


<hjopertham@yahoo.co.uk> wrote in message
news:1116428392.045398.114130@f14g2000cwb.googlegroups.com...
> Thanks Bob for your help & tips.
>
> > > 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?
>
>
> 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.
>