Call VBA Gurus - Help to Optimise two small UDF's
 

I have written two small VBA UDF's to solve a problem that I could not
solve through standard excel functions. I would love to have some
guru feedback on speeding them up, as they are going to be used very
heavily.

Here is sample data, no macros so u can safely open with macros
disabled (The spreadsheet has some comments to clarify what I am
doing):

http://s2.yousendit.com/d.aspx?id=B0...196A729CFE93FF

And here is a text file containing the two functions:

http://s11.yousendit.com/d.aspx?id=F...B0DD29262F7248

Thanks In Advance
Orekin

Call VBA Gurus - Help to Optimise two small UDF's
 

Orekin ,

I have written two small VBA UDF's to solve a problem that I could not
solve through standard excel functions.


Good News!

these CAN simply be done in standard worksheetfunction..
it's no more then a multi column lookup..

enter in c2:
=SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21)
copy it down the column..

and you're done :)

of course you could make it nicer by adding
some names...

names define..
CNnrs = offset($h$1,1,0,counta($h:$h)-1,1)
CNamt = offset(cnnrs,0,1)

now in c2 = SUMPRODUCT(--(A2=cnnrs),cnamt)


excel can be simple.. you just gotta learn how:)

<vbg


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool

Q for KeepITCool /
 

-- converts the boolean to number and is called a UNARY minus.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"ALEX" wrote:

In =SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21)

What does
(--($A2=$H$2:$H$21)

mean???


"orekin"
.com...
I have written two small VBA UDF's to solve a problem that I could not
solve through standard excel functions. I would love to have some
guru feedback on speeding them up, as they are going to be used very
heavily.

Here is sample data, no macros so u can safely open with macros
disabled (The spreadsheet has some comments to clarify what I am
doing):

http://s2.yousendit.com/d.aspx?id=B0...196A729CFE93FF

And here is a text file containing the two functions:

http://s11.yousendit.com/d.aspx?id=F...B0DD29262F7248

Thanks In Advance
Orekin

Q for KeepITCool /Again
 

arghh :)

sumproduct multi column lookup comes up in 5% of all questions here ...

it compares a2 with all cells in h2:h21
--(a2=h2:h21) produces a series of zeros and ones..

sumproduct(--(a2=h2:h21),i2:i21)
multiplies that series of 0/1 with value in i2:i21
and sum it...


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"ALEX" wrote:

OK, then what does the following do? Can you elaborate?
$A2=$H$2:$H$21


"keepITcool" дÈëÏûÏ¢ÐÂÎÅ
...
-- converts the boolean to number and is called a UNARY minus.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"ALEX" wrote:

In =SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21)

What does
(--($A2=$H$2:$H$21)

mean???


"orekin"
.com...
I have written two small VBA UDF's to solve a problem that I could
not solve through standard excel functions. I would love to have
some guru feedback on speeding them up, as they are going to be
used very heavily.

Here is sample data, no macros so u can safely open with macros
disabled (The spreadsheet has some comments to clarify what I am
doing):

http://s2.yousendit.com/d.aspx?id=B0...196A729CFE93FF

And here is a text file containing the two functions:

http://s11.yousendit.com/d.aspx?
id=F70160A5983AF60BEDB0DD29262F7248

Thanks In Advance
Orekin

Q for KeepITCool /Again
 

Just butting in...

Try this formula in another cell:
=SUMPRODUCT(--($A2=$H$2:$H$6),$I$2:$I$6)
(just a smaller range)

Now highlight ($A2=$H$2:$H$6) in the formula bar and hit F9.
You'll see something like:
=SUMPRODUCT(--{FALSE;TRUE;FALSE;TRUE;FALSE},$I$2:$I$6)
Now highlight the last minus through the closing right curly bracket
-{FALSE;TRUE;FALSE;TRUE;FALSE}
and hit F9

You'll see something like:
=SUMPRODUCT(-{0;-1;0;-1;0},$I$2:$I$6)
Now the same for: -{0;-1;0;-1;0}
and hit f9
you'll see:
=SUMPRODUCT({0;1;0;1;0},$I$2:$I$6)

Since =sumproduct() likes to deal with numbers, you had to convert those
true/falses to positive 1's and 0's.

The first unary minus converted true/falses to -1/0's and the second one made
the -1's, +1's.

=====
And if I highlight $i$2:$6 and hit f9, I get:
(with my test data)
=SUMPRODUCT({0;1;0;1;0},{3;3;5;2;0})

The I get 0*3 + 1*3 + 0*5 + 1*2 + 0*0

or just 5.

Remember to hit escape or edit|undo so you don't screw up your real formula when
evaluating this way.

ALEX wrote:

OK, then what does the following do? Can you elaborate?
$A2=$H$2:$H$21

"keepITcool" дÈëÏûÏ¢ÐÂÎÅ
...
-- converts the boolean to number and is called a UNARY minus.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"ALEX" wrote:

In =SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21)

What does
(--($A2=$H$2:$H$21)

mean???


"orekin"
.com...
I have written two small VBA UDF's to solve a problem that I could not
solve through standard excel functions. I would love to have some
guru feedback on speeding them up, as they are going to be used very
heavily.

Here is sample data, no macros so u can safely open with macros
disabled (The spreadsheet has some comments to clarify what I am
doing):

http://s2.yousendit.com/d.aspx?id=B0...196A729CFE93FF

And here is a text file containing the two functions:

http://s11.yousendit.com/d.aspx?id=F...B0DD29262F7248

Thanks In Advance
Orekin




--

Dave Peterson

Call VBA Gurus - Help to Optimise two small UDF's
 

Thanks!