Wednesday, September 26, 2012

Filter results with multiple conditions

Question:

 (by Kirsten)

 I'm using a Mac to make a spreadsheet of student data (see attached file).



In Column B, I have different grades (6,7,8,9,10,11,12)
In Column C, I have different levels (beginner, intermediate, advanced)

Depending on how these two pieces of data combine, Column D ("Total Weekly Periods Required") will change.
Some examples: 
Grade 6, Beginner needs 6 periods. I would like the number "6" to appear in Column D whenever Column B says "6" AND Column C says "Beginner."
Grade 12, Advanced needs 3 periods. I would like the number "3" to appear in Column D whenever Column B says "12" AND Column C says "Advanced."

There are 21 different combinations, and I do not know how to best format it so that Column D can populate itself. Help would be much appreciated.


Solution:

Make a sheet "Sheet2", which will have all the 21 records, have a look at the following screenshot:



And then in Sheet "Sheet 1", you need to put the following formula in Cell "D2":
=filter('Sheet2'!C2:C22;match(concat('Sheet2'!A2:A22;'Sheet2'!B2:B22);concat(B2:B;C2:C);0))

Have a look at the following screenshot:




I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

7 comments:

  1. The Walking CookbookSeptember 26, 2012 at 9:27 PM

    Kishan, this is fantastic. Thanks so much! You have just saved my team and me a boatload of time. -Kirsten

    ReplyDelete
  2. UnknownOctober 19, 2013 at 7:58 PM

    Hello,

    Thanks for this formula. I have tried it with success, but I'm having difficulties with copying the formula and inserting it into all cells below "D2" in your example. Do you have to change the reference in the formula to get the concat to work?

    My sheet can be seen here: https://docs.google.com/spreadsheet/ccc?key=0Atd0wKctw3H7dHhEQ1lCQWdaWENGc3RaenR2eWUyZVE&usp=sharing

    Thank you!

    ReplyDelete
    Replies
    1. KishOctober 19, 2013 at 8:23 PM

      Hi Jim,

      Try the following formula in Cell F2 of Sheet "Order":
      =index('Sheet2'!A:C;arrayformula(match(concat(D2;E2);concat('Sheet2'!A:A;'Sheet2'!B:B);false));3)

      and then drag it down fo F3,F4.. as far as needed...

      Delete
    2. UnknownOctober 19, 2013 at 10:36 PM

      Hello Kishan. It worked for F2, but it didn't work to copy the formula and paste it to F3 on down. I have it saved there if you wish to play with it. Try selecting Clear vs Cobalt vs Pink and look at the different quantities when you change Pink. Something isn't pointing correctly...

      Delete
    3. KishOctober 19, 2013 at 11:11 PM

      Hi Jim,

      You don't have to copy the formula to the cell below... you can drag that cell from the bottom-right corner to the cells below which will copy the formula to the cell below and it will auto change the formula to:
      =index('Sheet2'!A:C;arrayformula(match(concat(D3;E3);concat('Sheet2'!A:A;'Sheet2'!B:B);false));3)


      Delete
    4. KishOctober 19, 2013 at 11:13 PM

      Or you can also do the following thing:
      be on the cell F2 and then press "ctrl+c"
      and then go to cell F3 and then press "ctrl+v"
      doing so will also auto change the formula....

      Delete

Subscribe to: Post Comments (Atom)