PDA

View Full Version : Is formula solution available?



mdmackillop
11-23-2005, 01:52 PM
Hi all,
I'm wondering if there is a worksheet formula to allocate values from data in
columns based on row and column headings. I have a workable VBA solution.
Regards
MD

Ken Puls
11-23-2005, 02:11 PM
Yep. :)

I believe my numbers came out the same as what you have, but you many want to just double check it.

EDIT: Just for any who don't want to download the books... it needs a coerced sumproduct. Zack has a great article in the articles section on how to build these.

=SUMPRODUCT(--(BOM!$A$1:$A$859=Sheet2!$A5),--(BOM!$B$1:$B$859=Sheet2!D$1),BOM!$C$1:$C$859)

mdmackillop
11-23-2005, 02:19 PM
Thanks Ken
I knew there must be some way to do it. I'll brush up on my SumProduct before I try RegExp!
Regards
Malcolm

mdmackillop
11-23-2005, 03:28 PM
Part 2 - a step too far?
Can I collect Categories on Sheet3. For each Property and Category, a SumProduct? of Quantity x Rate x Uplift.
For information I'll have 45 categories from 160 columns and eventually 7000 rows, although formulae will periodically be changed to values to simplify things.

Shazam
11-23-2005, 05:34 PM
I'm putting my 2 cents in. You could also use this formula for your first question put this in cell D7.

=LOOKUP(2,1/((BOM!B$2:B$859=D$1)*(BOM!A2:A$859=A7)),BOM!C$2:C$858)

For your second question I'm not sure this is what you are looking for. On sheet3 put this in cell A4 and copy it down.


=LOOKUP(2,1/((Sheet2!B$7:B$100=B4)),Sheet2!D$7:$100)*Sheet2!D$2*Sheet2!D$3

Let me know if it works ?

Ken Puls
11-23-2005, 11:01 PM
Hi Malcolm,

Best I can do for tonight... unfortunately, I can't get it to look up based on the entire table, so am doing it one row at a time. ie:

=SUMPRODUCT(Sheet2!$D7:$K7,--(Sheet2!$D$4:$K$4=D$1))

It CAN be done on the entire table (changing D7:K7 to D7:K30), but I think it needs the n() funcion or something. Zack may happen along to get this before I do. Regardless, what I have does work, it just isn't as robust as I'd like. :)

Shazam, haven't played with your variation yet.

mdmackillop
11-24-2005, 04:13 PM
Hi Ken
Thanks for your assistance. A copy of my final version is attached.

Shazam,
Your formula was close, but through up some erroneous and unexpected seemingly random errors. Thanks for the efforts

Regards
Malcolm

Bob Phillips
11-25-2005, 03:46 AM
Hi Ken
Thanks for your assistance. A copy of my final version is attached.

Shazam,
Your formula was close, but through up some erroneous and unexpected seemingly random errors. Thanks for the efforts

Regards
Malcolm

Ken,

You use the * operator when using multiple columns.

Malcolm,

Formula

=SUMPRODUCT((Analysis!$D6:$FH23)*(Analysis!$D$2:$FH$2=D$2))

Ken Puls
11-25-2005, 10:03 AM
Cool! Thanks, Bob! I'm still trying to get my head completely wrapped around the power of sumproduct and exactly how to use it. :)

Bob Phillips
11-25-2005, 10:44 AM
Cool! Thanks, Bob! I'm still trying to get my head completely wrapped around the power of sumproduct and exactly how to use it. :)

Have you seen

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Also, I will doing a presentation on Advanced Counting ANd Summing at the UK Excel Users Conference, SP will figure pretty heavily in that. Jet on over http://vbaexpress.com/forum/images/smilies/001.gif

Ken Puls
11-25-2005, 11:02 AM
Jet on over http://vbaexpress.com/forum/images/smilies/001.gif

I'll see if my boss will pay for that! :rotlaugh:

Going to check out your writeup now...

Zack Barresse
11-25-2005, 02:09 PM
No, you shouldn't need a de-referencing with the N() function here Ken. Wrong situation. We're not talking about de-referencing, but multi-conditionals with multiple columns.

mdmackillop
11-25-2005, 03:09 PM
Have you seen

http://www.xldynamic.com/source/xld.SUMPRODUCT.html



Thanks for spoiling my leisurely weekend:bug: . The conference sound interesting though, have you a link to it?
Regards
Malcolm

Bob Phillips
11-25-2005, 03:35 PM
Thanks for spoiling my leisurely weekend:bug: . The conference sound interesting though, have you a link to it?
Regards
Malcolm

Malcolm,

The details are still being finalised, dates, venue, speakers etc., but Damon is seeking input http://peach.ease.lsoft.com/scripts/wa.exe?A2=ind0511b&L=excel-g&T=0&X=0396D95640A77C62C4&Y=bob.phillips%40dsl.pipex.com&P=5092

Hopefully this will be of interest to all our UK and near European colleagues. As well myself, Andy Pope and Nick Hodge are down to speak.

Ken Puls
11-25-2005, 05:05 PM
No, you shouldn't need a de-referencing with the N() function here Ken. Wrong situation. We're not talking about de-referencing, but multi-conditionals with multiple columns.

Uh... yeah... right.

I just worked out the double unary part, so cut me some slack, eh? :rotlaugh:

Zack Barresse
11-27-2005, 11:33 AM
Will there be video feed or recordings of this?