MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selecting data from a table based upon two conditions


Posted by Mike on October 29, 2001 4:47 AM

Hello,

I think this must be able to be done, but i am having trouble coming up with the solution. I have a table of data with the columns as such:

CODE; DATE; DESCR; MARKET; SQTY; SVAL

I need to extract a figure from SALESQTY and SALESVAL based up looking up a combination of a specific string in the CODE column and a specific string in the MARKET column, and return this to a cell in another sheet. There can be up to 11 different MARKETs for each CODE, and this CODE is repeated on each row where the MARKET is different.

A sample is below:

CODE DATE DESCR MARKET SQTY SVAL
0140863737 7/12/95 SS Home 45 177
0140863737 7/12/95 SS Export 1 3
0140863737 7/12/95 SS Australia 4 6
0140863974 28/08/97 AW Home -10 -39
0140864083 4/07/96 FQ Home 27 110
0140864083 4/07/96 FQ Export 9 31


In the first CODE there are 3 markets. So basically I am looking to ask the question "What is the value in column SQTY where the CODE=X and the MARKET=Y ?" and the same for the value in SVAL.

If anybody can help me it would be greatly appreciated.

Thanks,

Mike


Posted by Juan Pablo on October 29, 2001 4:49 AM

Have you tried ussing the search assistant (If you don't see it in the tools menu you need to install it, it's an AddIn), it'll guide you to the thing you're trying to do.

Juan Pablo

Posted by Mike on October 29, 2001 4:59 AM

Don't seem to have this, is it in Excel 97? Also, i need this process written in a formula that references this table from another sheet. Will this be possible using the Seach Assistant you mentioned?

thanks

Posted by Juan Pablo on October 29, 2001 5:02 AM

Do this:

First go to Tools, AddIns.

From there look for something like "Search Assistant", i don't have the exact name, but it's something like that.

Activate it if not activated.

Now, go again to Tools, Assistants, and select the search assistant. It'll show a series of steps that will guide you and will result in a formula, so you can change parameters and be able to recalculate.

And yes, it's in Excel 97 (I'm using it)

Juan Pablo Don't seem to have this, is it in Excel 97? Also, i need this process written in a formula that references this table from another sheet. Will this be possible using the Seach Assistant you mentioned? thanks

Posted by Mike on October 29, 2001 5:11 AM

Thanks Juan,

Sounds perfect but the Add-in doesn't come standard with the Excel installation that our IT dept has set up, and is not available from the list of add-ins. Hopefully our IT dept will be able to help.

Mike

Posted by Juan Pablo on October 29, 2001 5:24 AM

Ok then...

try with this formula. I assumed your data is in A1:F200

Put in H1 "CODE", I1 "MARKET" and J1 "SQTY"

Now in H2 put one for your codes, for example: 140864083

In I2 one of your markets. "Home"

Now in J2 put this formula

=SUMAPRODUCTO(($A$2:$A$200=H2)*($D$2:$D$200=I2)*($E$2:$E$200))

To change this formula to retrieve SVAL, change E2:E200 to F2:F200

Juan Pablo

Posted by lenze on October 29, 2001 5:37 AM

Check this article out:
http://www.elementkjournals.com/ime/0109/ime0191.htm

Posted by Mike on October 29, 2001 5:42 AM

Re: Ok then...

thanks very much, i owe you a drink.

Posted by Mike on October 29, 2001 6:23 AM

Re: Ok then...

OK, just one more variation. What if the same CODE and MARKET exist exist more than once on particular rows, and I just want to extract SQTY from the first instance.

Mike

Posted by Juan Pablo on October 29, 2001 10:17 AM

Re: Ok then...

Ok Mike, try this one...

Still in H2 and I2 are your conditions. In J2 put

=INDIRECT("E"&MATCH(H2&I2,$A$2:$A$200&$D$2:$D$200,0)

To enter this formula you should press Control Shift Enter at the same time.

To get the value of SVAL change "E" to "F" and that's it !

Juan Pablo OK, just one more variation. What if the same CODE and MARKET exist exist more than once on particular rows, and I just want to extract SQTY from the first instance. Mike

Posted by Stewart Cooke on October 31, 2001 3:56 AM

Have you tried using the DSum Function ?

Structure of Function:-
=DSUM(DatabaseRange,"MeasureToSum",Criteria Range)

The First range should cover all your data, including column titles (i.e Code,Date,Descr).

You then need to specify what measure you wish to sum (in your example this would be SQTY). Use speech marks around this measure.

Finally you need to specify the criteria range. For your query this would look like this:-

Code Market
X Y

Assuming the first range had been named TABLE1 and the Criteria range had the name Output1, then the formula would be as follows:-

=DSum(Table1,"SQTY",Output1)

This would sum SQTY where Code=X and Market=Y
To sum SVal in a similar way simply substitude "SQTY" for "SVal" in the formula.

Excel help gives further examples, and may be easier to follow than my brief note.