Return a list of names based on multiple criteria

  • Hi all, I am massively struggling to crack the following problem. I need to pick up a list of students who are not making enough progress at school. The progress is quantified (above 0 is outstanding progress, =0 is good, =-1 satisfactory and below -1 is inadequate. Students are from a variety of classes (i.e. class 10a, class 10b, class 10c, etc). The main spreadsheet contains all the data for each student and looks like this:



    [TABLE="class: cms_table, width: 465"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Gender

    [/td]


    [td]

    Class

    [/td]


    [td]

    Progress

    [/td]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    Sam

    [/td]


    [td]

    F

    [/td]


    [td]

    10b

    [/td]


    [td]

    -3

    [/td]


    [/tr]


    [tr]


    [td]

    Ella

    [/td]


    [td]

    F

    [/td]


    [td]

    10c

    [/td]


    [td]

    -1

    [/td]


    [/tr]


    [tr]


    [td]

    Robert

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [td]

    -1

    [/td]


    [/tr]


    [tr]


    [td]

    Bob

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    Harry

    [/td]


    [td]

    M

    [/td]


    [td]

    10c

    [/td]


    [td]

    -5

    [/td]


    [/tr]


    [tr]


    [td]

    Robbie

    [/td]


    [td]

    M

    [/td]


    [td]

    10b

    [/td]


    [td]

    5

    [/td]


    [/tr]


    [tr]


    [td]

    Jack

    [/td]


    [td]

    M

    [/td]


    [td]

    10c

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    Adam

    [/td]


    [td]

    M

    [/td]


    [td]

    10c

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    Sarah

    [/td]


    [td]

    F

    [/td]


    [td]

    10c

    [/td]


    [td]

    -1

    [/td]


    [/tr]


    [tr]


    [td]

    Vanessa

    [/td]


    [td]

    F

    [/td]


    [td]

    10c

    [/td]


    [td]

    -1

    [/td]


    [/tr]


    [tr]


    [td]

    Amy

    [/td]


    [td]

    F

    [/td]


    [td]

    10b

    [/td]


    [td]

    4

    [/td]


    [/tr]


    [tr]


    [td]

    Ben

    [/td]


    [td]

    M

    [/td]


    [td]

    10b

    [/td]


    [td]

    -2

    [/td]


    [/tr]


    [tr]


    [td]

    Brian

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    Keeley

    [/td]


    [td]

    F

    [/td]


    [td]

    10a

    [/td]


    [td]

    -1

    [/td]


    [/tr]


    [tr]


    [td]

    Emma

    [/td]


    [td]

    F

    [/td]


    [td]

    10a

    [/td]


    [td]

    -1

    [/td]


    [/tr]


    [tr]


    [td]

    Jan

    [/td]


    [td]

    F

    [/td]


    [td]

    10a

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    Ryan

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [/TABLE]



    How do I pick up a list of students who are making inadequate progress based on class? I also need the names of the females in the list to be highlighted in yellow.


    The resultant list should look like this[TABLE="class: cms_table, width: 58"]

    [tr]


    [td][/td]


    [/tr]


    [/TABLE]


    [TABLE="class: cms_table, width: 58"]

    [tr]


    [td][/td]


    [/tr]


    [/TABLE]


    [TABLE="class: cms_table, width: 416"]

    [tr]


    [td]

    Class

    [/td]


    [td]

    10a

    [/td]


    [td]

    Outstanding

    [/td]


    [td]

    Good

    [/td]


    [td]

    Satisfactory

    [/td]


    [td]

    Inadequate

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    John

    [/td]


    [td]

    Bob

    [/td]


    [td]

    Robert

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    Brian

    [/td]


    [td]

    Jan

    [/td]


    [td]

    Keeley

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Ryan

    [/td]


    [td]

    Emma

    [/td]


    [/tr]


    [/TABLE]




    Thank you

  • Re: Return a list of names based on multiple criteria


    Assuming your database is in Sheet1!A1:D19, and assuming your summary is in Sheet2, starting at A1, with 10a criteria in B1 and results starting in C2, try the following:


    In Sheet2, C2 enter formula:


    [COLOR="#0000FF"]=IFERROR(INDEX(Sheet1!$A$2:$A$19,SMALL(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$D$2:$D$19[COLOR="#FF0000"]>1[/COLOR],ROW(Sheet1!$A$2:$A$19)-ROW(Sheet1!$A$2)+1)),ROWS(C$2:C2))),"")[/COLOR]


    confirmed with CTRL+SHIFT+ENTER, not just ENTER and copied down as far as you need to ensure all results are captured.


    Repeat the formula in the other column change the >1 condition to suit the respective condition to check.


    For the conditional formatting, select from C2 to bottom right corner of the results range. Go to Home|Conditional Formatting|New Rule.
    Select "use a formula to determine which cells to format"
    Enter formula:


    [COLOR="#0000FF"]=COUNTIFS(Sheet1!$A$2:$A$19,C2,Sheet1!$B$2:$B$19,"F")[/COLOR]


    Then click Format and choose colour from Fill tab.


    Click Ok, then click Ok again to apply.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return a list of names based on multiple criteria


    Hi,


    Thanks for this but unfortunately it did not work. Do you mind explaining how this formula is supposed to work or would it be possible to send me a sample spreadsheet with this in full function?

  • Re: Return a list of names based on multiple criteria


    tHIS IS FANTASTIC. I can't believe it works so well. Just one problem, if the progress is blank it still picks up the name. i.e. if I delete John's progress value of 3 in sheet 1, his name still appears in sheet 2 whereas it should not. Why is this?

  • Re: Return a list of names based on multiple criteria


    Exel sees the blank as equal to 0, try revising the formula in D2 to:


    [COLOR="#0000FF"]=IFERROR(INDEX(Sheet1!$A$2:$A$19,SMALL(IF(Sheet1!$C$2:$C$19=$B$1,IF((Sheet1!$D$2:$D$19=0)*(Sheet1!$D$2:$D$19<>""),ROW(Sheet1!$A$2:$A$19)-ROW(Sheet1!$A$2)+1)),ROWS(D$2:D2))),"")[/COLOR]


    confirmed with CTRL+SHIFT+ENTER not just ENTER.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return a list of names based on multiple criteria


    This has worked well. Thanks. I have another question which is related....How do I populate a similar list but this time there are three conditions:


    1- By class name
    2- In order of progress (largest number to lowest)
    3- By gender


    So for example a typical list would consist of the progress amount (in descending order) for boys in 10a .

  • Re: Return a list of names based on multiple criteria


    If you are able to also list the Progress in the summary sheet,? Then I can give the sorted list, otherwise, you would have to presort the database list by class, then by Progess (or by class, then gender, then progress), so that the results would be sorted.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return a list of names based on multiple criteria


    Ok,


    So using my last sample attachment, insert a column between C and D. In this new column D use formula:


    [COLOR="#0000FF"]=IFERROR(LARGE(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$D$2:$D$19>1,IF(Sheet1!$B$2:$B$19="M",Sheet1!$D$2:$D$19))),ROWS(C$2:C2)),"")[/COLOR]


    confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.


    In C2 now use:


    [COLOR="#0000FF"]=IF(D2="","",INDEX(Sheet1!$A$2:$A$19,SMALL(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$B$2:$B$19="M",IF(Sheet1!$D$2:$D$19=D2,ROW(Sheet1!$D$2:$D$19)-ROW(Sheet1!$D$2)+1))),COUNTIF(D$2:D2,D2))))[/COLOR]


    confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.



    do similarly for others, if needed/desired

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return a list of names based on multiple criteria


    I have followed the above instructions but not getting the desired result. In fact it is not producing a list of names with progress rated in a descending order.

  • Re: Return a list of names based on multiple criteria


    Just to clarify. The summary sheet needs to appear as follows:


    [TABLE="width: 768"]




    [tr]


    [TD="width: 64, bgcolor: transparent"]Class[/TD]


    [TD="width: 64, bgcolor: transparent"]10a[/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [TD="width: 64, bgcolor: transparent"]Class[/TD]


    [TD="width: 64, bgcolor: transparent"]10b[/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [TD="width: 64, bgcolor: transparent"]Class[/TD]


    [TD="width: 64, bgcolor: transparent"]10c[/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [/tr]


    [tr]


    [TD="class: xl64, width: 64, bgcolor: transparent"]Name[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Gender[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Class[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Progress[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl64, width: 64, bgcolor: transparent"]Name[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Gender[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Progress[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl64, width: 64, bgcolor: transparent"]Name[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Gender[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Progress[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, width: 64, bgcolor: transparent"]John[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]10a[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]3[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Robbie[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]5[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Jack[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Brian[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]10a[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]3[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Amy[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]4[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Adam[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Bob[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]10a[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Ben[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]-2[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Ella[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Jan[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]10a[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Sam[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]-3[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Sarah[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Ryan[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]10a[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Vanessa[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Robert[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]10a[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Harry[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]-5[/TD]


    [/tr]


    [tr]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Keeley[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]10a[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [/tr]


    [tr]


    [TD="class: xl63, width: 64, bgcolor: transparent"]Emma[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent"]10a[/TD]


    [TD="class: xl63, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [/tr]



    [/TABLE]

  • Re: Return a list of names based on multiple criteria


    Rather it should be:


    [TABLE="width: 704"]




    [tr]


    [TD="width: 64, bgcolor: transparent"]Class[/TD]


    [TD="width: 64, bgcolor: transparent"]10a[/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [TD="width: 64, bgcolor: transparent"]Class[/TD]


    [TD="width: 64, bgcolor: transparent"]10b[/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [TD="width: 64, bgcolor: transparent"]Class[/TD]


    [TD="width: 64, bgcolor: transparent"]10c[/TD]


    [TD="width: 64, bgcolor: transparent"][/TD]


    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [/tr]


    [tr]


    [TD="class: xl66, width: 64, bgcolor: transparent"]Name[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Gender[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Progress[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl66, width: 64, bgcolor: transparent"]Name[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Gender[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Progress[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl66, width: 64, bgcolor: transparent"]Name[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Gender[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Progress[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]John[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]3[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Robbie[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]5[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Jack[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Brian[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]3[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Amy[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]4[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Adam[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Bob[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Ben[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-2[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Ella[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Jan[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Sam[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-3[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Sarah[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Ryan[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]0[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Vanessa[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Robert[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Harry[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]M[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-5[/TD]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Keeley[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64, bgcolor: transparent"]Emma[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent"]F[/TD]


    [TD="class: xl65, width: 64, bgcolor: transparent, align: right"]-1[/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [TD="bgcolor: transparent"][/TD]


    [/tr]



    [/TABLE]

  • Re: Return a list of names based on multiple criteria


    Didn't you say one of your criteria was only Males?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return a list of names based on multiple criteria


    I had assumed you still want them separated for Outstanding, etc....


    As one list, and if you want only Males, then try in D2:


    [COLOR="#0000FF"]=IFERROR(LARGE(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$B$2:$B$19="M",Sheet1!$D$2:$D$19)),ROWS(C$2:C2)),"")[/COLOR]


    confirmed with CTRL+SHIFT+ENTER, and copy down.


    Formula in C2 won't change.


    If you want both genders, then in D2:


    [COLOR="#0000FF"]=IFERROR(LARGE(IF(Sheet1!$C$2:$C$19=$B$1,Sheet1!$D$2:$D$19),ROWS(C$2:C2)),"")[/COLOR]


    and in C2:


    [COLOR="#0000FF"]=IF(D2="","",INDEX(Sheet1!$A$2:$A$19,SMALL(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$D$2:$D$19=D2,ROW(Sheet1!$D$2:$D$19)-ROW(Sheet1!$D$2)+1)),COUNTIF(D$2:D2,D2))))[/COLOR]


    both CSE confirmed and copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return a list of names based on multiple criteria


    ok that works. What formulae are needed if I want to split the list into two, i.e. one list for males and one list for females

  • Re: Return a list of names based on multiple criteria


    Assuming list for males will be in column C:D,


    In C2:


    [COLOR="#0000FF"] =IF(D2="","",INDEX(Sheet1!$A$2:$A$19,SMALL(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$B$2:$B$19="M",IF(Sheet1!$D$2:$D$19=D2,ROW(Sheet1!$D$2:$D$19)-ROW(Sheet1!$D$2)+1))),COUNTIF(D$2:D2,D2))))[/COLOR]


    in D2:


    [COLOR="#0000FF"] =IFERROR(LARGE(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$B$2:$B$19="M",Sheet1!$D$2:$D$19)),ROWS(C$2:C2)),"")[/COLOR]


    for Females, assuming in columns E:F


    in E2:


    [COLOR="#0000FF"] =IF(D2="","",INDEX(Sheet1!$A$2:$A$19,SMALL(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$B$2:$B$19="F",IF(Sheet1!$D$2:$D$19=F2,ROW(Sheet1!$D$2:$D$19)-ROW(Sheet1!$D$2)+1))),COUNTIF(F$2:F2,F2))))[/COLOR]


    in F2:


    [COLOR="#0000FF"] =IFERROR(LARGE(IF(Sheet1!$C$2:$C$19=$B$1,IF(Sheet1!$B$2:$B$19="F",Sheet1!$D$2:$D$19)),ROWS(E$2:E2)),"")[/COLOR]


    ALL CSE Confirmed and Copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return a list of names based on multiple criteria


    So far so good. I have added an additional column to my original data in sheet 1 for the test score:


    [TABLE="width: 390"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Gender

    [/td]


    [td]

    Class

    [/td]


    [td]

    Progress

    [/td]


    [td]

    Test Score

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [TD="align: right"]3[/TD]
    [TD="align: right"]73[/TD]

    [/tr]


    [tr]


    [td]

    Sam

    [/td]


    [td]

    F

    [/td]


    [td]

    10b

    [/td]


    [TD="align: right"]-3[/TD]
    [TD="align: right"]35[/TD]

    [/tr]


    [tr]


    [td]

    Ella

    [/td]


    [td]

    F

    [/td]


    [td]

    10c

    [/td]


    [TD="align: right"]-1[/TD]
    [TD="align: right"]55[/TD]

    [/tr]


    [tr]


    [td]

    Robert

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [TD="align: right"]-1[/TD]
    [TD="align: right"]52[/TD]

    [/tr]


    [tr]


    [td]

    Bob

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [TD="align: right"]0[/TD]
    [TD="align: right"]65[/TD]

    [/tr]


    [tr]


    [td]

    Harry

    [/td]


    [td]

    M

    [/td]


    [td]

    10c

    [/td]


    [TD="align: right"]-5[/TD]
    [TD="align: right"]15[/TD]

    [/tr]


    [tr]


    [td]

    Robbie

    [/td]


    [td]

    M

    [/td]


    [td]

    10b

    [/td]


    [TD="align: right"]5[/TD]
    [TD="align: right"]80[/TD]

    [/tr]


    [tr]


    [td]

    Jack

    [/td]


    [td]

    M

    [/td]


    [td]

    10c

    [/td]


    [TD="align: right"]0[/TD]
    [TD="align: right"]69[/TD]

    [/tr]


    [tr]


    [td]

    Adam

    [/td]


    [td]

    M

    [/td]


    [td]

    10c

    [/td]


    [TD="align: right"]0[/TD]
    [TD="align: right"]68[/TD]

    [/tr]


    [tr]


    [td]

    Sarah

    [/td]


    [td]

    F

    [/td]


    [td]

    10c

    [/td]


    [TD="align: right"]-1[/TD]
    [TD="align: right"]56[/TD]

    [/tr]


    [tr]


    [td]

    Vanessa

    [/td]


    [td]

    F

    [/td]


    [td]

    10c

    [/td]


    [TD="align: right"]-1[/TD]
    [TD="align: right"]57[/TD]

    [/tr]


    [tr]


    [td]

    Amy

    [/td]


    [td]

    F

    [/td]


    [td]

    10b

    [/td]


    [TD="align: right"]4[/TD]
    [TD="align: right"]75[/TD]

    [/tr]


    [tr]


    [td]

    Ben

    [/td]


    [td]

    M

    [/td]


    [td]

    10b

    [/td]


    [TD="align: right"]-2[/TD]
    [TD="align: right"]45[/TD]

    [/tr]


    [tr]


    [td]

    Brian

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [TD="align: right"]3[/TD]
    [TD="align: right"]74[/TD]

    [/tr]


    [tr]


    [td]

    Keeley

    [/td]


    [td]

    F

    [/td]


    [td]

    10a

    [/td]


    [TD="align: right"]-1[/TD]
    [TD="align: right"]53[/TD]

    [/tr]


    [tr]


    [td]

    Emma

    [/td]


    [td]

    F

    [/td]


    [td]

    10a

    [/td]


    [TD="align: right"]-1[/TD]
    [TD="align: right"]54[/TD]

    [/tr]


    [tr]


    [td]

    Jan

    [/td]


    [td]

    F

    [/td]


    [td]

    10a

    [/td]


    [TD="align: right"]0[/TD]
    [TD="align: right"]67[/TD]

    [/tr]


    [tr]


    [td]

    Ryan

    [/td]


    [td]

    M

    [/td]


    [td]

    10a

    [/td]


    [TD="align: right"]0[/TD]
    [TD="align: right"]65[/TD]

    [/tr]


    [/TABLE]



    Now in instances where the progress is equal, e.g. Brian and John have '3' progress in class 10a, how do I further rank according to test score. The spreadsheet with your current formulae is showing John at the top with Brian second, however Brian has a higher test score albeit the same progress level of 3. On this basis Brian should be ranked higher. Please provide formulae for this, both as a mixed list and also with male/female separated.


    Many thanks once again.

  • Re: Return a list of names based on multiple criteria


    I wish you would give all the facts straight from the start, really.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Return a list of names based on multiple criteria


    Sorry my friend....I only realised these things as we have gone on. As you work on a spreadsheet it often opens a can of worms, we're almost there...I'm sure this is the last step to it. Thank you for your patience. If I had your skill I would've dealt with it by now!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!