Sumproduct to Table...

[DonkeyOte]

Does anyone know of a utility / function or have themselves coded something that permits an end user to analyse a given Sumproduct formula in greater detail - ie returning the results of the generated array into tabular form ?

I've just sat down thinking this would be quite useful, ie I click on any sumproduct formula and run a routine to "Analyse" which in turn generates a new sheet with a matrix of the results...

However having just started coding this myself it is now dawning on me that this could prove very difficult (read beyond my abilities) ... breaking out the formula itself etc into component parts is pretty easy but actually evaluating each part is a little more difficult.

I suspect someone knows of an elegant way to do this and I'd love to see it... it may of course be that you can do this already but I don't know how...

Thanks,
Luke

 

[DonkeyOte]

So having looked into it in more depth it's seemingly to do with the 1st condition not being a standard "range = value" scenario (it's a match function)... this is a point I think you made in your earlier post so apologies if I've jumped the gun... I guess I need to go off and learn a little more ...

 

[cornflakegirl]

Rory - just trying to work my way through the code...

What does rng.Parent.Select mean / do?

 

[DonkeyOte]

should return you to original sheet (in which formula you are analysing resides)

key is to ensure you're on that sheet prior to evaluating the formula else relative references will go awry

(did that sound like I know what I'm talking about... not even remotely ?)

 

[RoryA]

It reselects the sheet with the formula - otherwise the Evaluate function evaluates the addresses against the new blank sheet!

 

[cornflakegirl]

Because the parent of rng is the worksheet?

Thanks both!

(It's quite traumatising to find that there's an approved use for .Select ... )

 

[DonkeyOte]

Aye...

Now can you tell me why the ISNUMBER(MATCH(range,{array},0)) in my example doesn't return an array when Evaluated unlike the other sub components ? hmmm.... well...

This is driving me crazy... so close...yet so far... grr...

PS. Having read up on Evaluate and having only previously used it in conjunction with SUMPRODUCT I am now beginning to see the flexibility it offers... why is it not used more often I wonder ?

 

[cornflakegirl]

Now it's bugging me too!

Although, in my searching, I have discovered that if we replace

varOutput = Application.Evaluate(strTemp)
with
varOutput = rng.Parent.Evaluate(strTemp)

in Rory's code, we can lose the .Select line. Which is nice

 

[DonkeyOte]

Oh I see.... you're using Rory's code eh .. now why would you do that ?

Re: worksheet.evaluate -- yes I saw that too but hadn't updated yet -- nice to know it will work.

I put Rory's code into mine (Step 4), Step 3 was/is attempt to handle situations where within the formula the "delimiter" may appear that was not in fact a valid argument delimiter, eg

=SUMPRODUCT(--(A1:A100=","),B1:B100)

This all works (though I had to add a bit of tweak to cater for sheet names encased within apostrophe's at the commencement of an argument...) but I can't resolve this issue of certain formulae not generating arrays

Emma we simply can not let this defeat us (me) !
I see this as perhaps our (my) only opportunity to ever actually contribute something truly worthwhile (worthwhile in my eyes at least!)

(This still counts regardless of the fact Rory coded it )

 

[cornflakegirl]

I'm waiting till your code is as beautiful as his!
(Actually, am mainly scared of yours - the delimiter stuff looks a bit complicated!)

I see you're putting yourself down again - think you need to take a leaf out of Rory's book there!

Now go and find out why EVALUATE isn't doing what it says on the tin!

 

[sous2817]

I for one and rooting for you both! I'll never have to hear "but who makes up that number" again! GO TEAM, GO!!