Return a list of names based on multiple criteria

excel_321 · Jul 14th 2014

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]

[/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]

[/tr]

[tr]

[td][/td]

[td]

John

[/td]

[td]

Bob

[/td]

[td]

Robert

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Brian

[/td]

[td]

Jan

[/td]

[td]

Keeley

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Ryan

[/td]

[td]

Emma

[/td]

[/tr]

[/TABLE]

Thank you

**NBVC** · Jul 14th 2014

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.

excel_321 · Jul 14th 2014

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?

**NBVC** · Jul 14th 2014

Re: Return a list of names based on multiple criteria

See attached.

Let me know if you still have trouble.

excel_321 · Jul 14th 2014

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?

**NBVC** · Jul 14th 2014

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.

excel_321 · Jul 17th 2014

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 .

**NBVC** · Jul 17th 2014

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.

excel_321 · Jul 17th 2014

Re: Return a list of names based on multiple criteria

yes I would like the progress listed in the summary also...sorry forgot to mention this

**NBVC** · Jul 17th 2014

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

excel_321 · Jul 17th 2014

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.

excel_321 · Jul 17th 2014

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"]Class[/TD]

[TD="width: 64, bgcolor: transparent"]10b[/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]

[/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="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="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]

[/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]

[/tr]

[/TABLE]

excel_321 · Jul 17th 2014

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"]Class[/TD]

[TD="width: 64, bgcolor: transparent"]10b[/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]

[/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="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="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]

[/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]

[/tr]

[/TABLE]

**NBVC** · Jul 17th 2014

Re: Return a list of names based on multiple criteria

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

**NBVC** · Jul 17th 2014

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.

excel_321 · Jul 17th 2014

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

**NBVC** · Jul 17th 2014

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.

excel_321 · Jul 17th 2014

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.

**NBVC** · Jul 17th 2014

Re: Return a list of names based on multiple criteria

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

excel_321 · Jul 17th 2014

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!

Return a list of names based on multiple criteria

Participate now!

Share