Microsoft Excel - need formula to select unique values based on certian criteria

Asked By Ewa P on 30-Jan-13 11:03 AM
Hi,
I need a list of classes (unique) by subject and year group.
at the moment i have a formula (sheet En, cell A43)
=IF(ROWS(A$42:A42)<=COUNTIF(calc!$AO$2:$AO$305,"English"),INDEX(calc!$AN$2:$AN$305,SMALL(IF(calc!$AO$2:$AO$305="English",ROW(calc!$AN$2:$AN$305)-ROW(calc!$AO$2)+1),ROWS(A$42:A42))),"")

The subjects list is in sheet calc columns AK, class list in columns AJ
how can I add a year group criteria to that formula? (year group is Sheet En, cell B2, year group woudl be part of class s name so I woudl need to use find somewhere)

sorry, I'm not able to attach a spreadhseet, document manager on the website comes blank (in Google chrome and IE) 

Thank you
Ewa P
Harry Boughen replied to Ewa P on 17-Feb-13 10:20 PM
Hello Ewa,
Perhaps if you show a snippet of the data that you are working with and the result that you would like to get from it we might be better able to help rather than trying to fathom and extend your existing formula.
Harry
Ewa P replied to Harry Boughen on 18-Feb-13 03:09 AM
Hello Harry, I think I have duplicated the post, sorry! I managed to upload the file here:
http://www.eggheadcafe.com/community/excel/66/10471013/formula-to-create-dynamic-range-based-on-2-criterias.aspx
Thank you
Ewa
Harry Boughen replied to Ewa P on 19-Feb-13 05:46 PM
Hi Ewa,
I think this does what you want but I had to add a helper column and cell to do it reasonably easily.
unique values based on criteria.zip
Hope this helps.
Harry
Ewa P replied to Harry Boughen on 22-Feb-13 07:42 AM
it works magic, thank you!