1st place, 2nd place winner

[erutherford]

I have a sheet titled "scores". Columns are name (text), division (text) and Score (number). I would like create another sheet (Awards) that displays the 1st and 2nd place winners (highest scores) in each division from the "scores" sheet.

example: Name Division Score
Bob Full 299
Joe Street 298
Bill Full 297
Ted Street 296

Thanks in advance and I'll keep searching the forum.

 

[Eric W]

Perhaps:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Division	Score			Division	First	Second	Third	Fourth
2	Bob	Full	299			Full	Al	Bob	Xavier	Bill
3	Joe	Street	298			Street	Joe	Ted
4	Bill	Full	297			Avenue	Mark	Mary	Alice	Jake
5	Ted	Street	296
6	Al	Full	300			Division	First		Second		Third		Fourth
7	Mark	Avenue	261			Full	300	Al	299	Bob	299	Xavier	297	Bill
8	Jake	Avenue	251			Street	298	Joe	296	Ted
9	Mary	Avenue	260			Avenue	261	Mark	260	Mary	260	Alice	251	Jake
10	Alice	Avenue	260
11	Xavier	Full	299
12

<tbody>
</tbody>

Scores​

Array Formulas Cell Formula G2 {=IFERROR(INDEX(Scores!$A$2:$A$20,SMALL(IF(COUNTIF($F2:F2,Scores!$A$2:$A$20)=0,IF(Scores!$B$2:$B$20=$F2,IF(Scores!$C$2:$C$20=MAX(IF(COUNTIF($F2:F2,Scores!$A$2:$A$20)=0,IF(Scores!$B$2:$B$20=$F2,Scores!$C$2:$C$20))),ROW(Scores!$A$2:$A$20)-ROW(Scores!$A$2)+1))),1)),"")} G7 {=IFERROR(LARGE(IF(Scores!$B$2:$B$20=$F7,Scores!$C$2:$C$20),COLUMNS($F7:G7)/2),"")} H7 {=IFERROR(INDEX(<fo Scores!$A$2:$A$20,SMALL(IF(Scores!$B$2:$B$20=$F7,IF(Scores!$C$2:$C$20=G7,ROW(Scores!$A$2:$A$20)-ROW(Scores!$A$2)+1)),COUNTIF($G7:G7,G7))</fo ),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If you just want the names, use the G2 formula. Copy down and across as desired. If you want names and scores, use the G7:H7 formulas. Put them in and copy them as a pair, and paste them down and across as desired.

You can also use a Pivot Table. Select A1:G11, Insert Pivot Table, put Division - Score - Name into the Rows box (in that order), right click on the score in A5, select Sort Largest to Smallest. Somewhat awkward display though. There's probably a better way.

 

[erutherford]

Thanks Eric for your help. I been dissecting it so I can understand what is happening. I made a test worksheet and made two separate tables. One for sample G2 and one for the others. Pasted the statements in and the results are all zero. I thought we could upload a sample file, but I guess not.

When you say "paste as a pair", what separates the two statements?

I am trying to get the easiest one working first (just the name), then move to the move complex one.

thanks again for your help

 

[Eric W]

Hmm. These are tricky formulas, so it might take a bit to understand them and get them working. A few tips: these are array formulas. This means that they work on a range of data, and you have to enter them using Control+Shift+Enter, or they won't work. So set up your sample sheet, select the cell where you want the formula, paste the formula in the formula bar, then hold down the Control and Shift keys, then press Enter. If you don't do that, the formula won't work right, and you might get the zero results.

Next, if you're trying to dissect them, you might be better off starting with the 2 formula set. The single formula is basically a combination of the 2 smaller formulas. When I say, "copy pairwise", in the example above, put the G7 formula in and confirm with CSE. Put the H7 formula in and confirm with CSE. Then select both cells (G7:H7), copy them (Control-C, or right click and select Copy), then select the range you want to put them (I7:N7), and paste them (Control-V or right click and select Paste). Then select G8:N9 and paste again.

To adapt the formulas for your sheet, you'll obviously need to change the ranges in the formulas. If you have trouble doing so, set up your sheets just like my example, and when it's working, just add/delete rows/columns until it matches what you want, Excel will adapt the formulas for you.

Finally, you can't upload a sheet to the forum, but you can use a tool such as the HTML Maker (see my signature) to show a sample like I did. You can also upload a file to a file sharing service (like Dropbox) and post a link, but many people will not download files from the internet, limiting the number of people who might help you.

Let me know if you have further questions.

 

[erutherford]

Eric,
First, thanks for taking the time to "spell it out" for me. That is what made it possible for me (a novice) to understand what was going on, apply it to my situation and it now works. Huge help!
Thanks again!

 

[Eric W]

Glad you got it working!

I've learned a lot here and elsewhere, and I'm happy to pass some of it along. Thanks for the feedback.

 

[erutherford]

Eric can you have two sets of criteria in the logic array?

=IFERROR([COLOR=Blue]LARGE([COLOR=Red]IF([COLOR=Green]Scores!$B$2:$B$20=$F7[/COLOR][/COLOR][/COLOR]

. So $F7 is one criteria, can you have more than one in the same array?

People enter their car in Full concours, then the car is further divided into subcategories of A,B or C.
Example: Category = Full (F7)
Sub category = A model (?)

thanks again

 

[Indystick]

Before answering your question, I want to offer a suggestion: name your ranges, then you don't have to include the worksheet names in the cell references. It will clear out the functions significantly.

Okay, if I understand your question correctly, you want to test on two criteria to select a value for a particular place (e.g., list the person that came in 2nd in the Full-A division (assuming A is the subdivision). In the example below I have named the ranges of values Name, Division, Subdivision, Score:

	A	B	C	D	E	F	G	H
1	Name	Division	Subdivision	Score			Division:	Avenue
2	Bob	Full	A	299			Subdivision:	C
3	Joe	Street	B	298
4	Bill	Full	B	297			1st:	Mark
5	Ted	Street	A	296			2nd:	Alice
6	Al	Full	A	300
7	Mark	Avenue	C	261
8	Jake	Avenue	B	251
9	Mary	Avenue	A	260
10	Alice	Avenue	C	260
11	Xavier	Full	A	299

<tbody>
</tbody>

For H5 (1st place), I have the following formula:

{ =IFERROR(INDEX(Name,MATCH(1,(H2=Division)*(H3=Subdivision)*(MAX(IF(Division=H2,IF(Subdivision=H3,Score)))=Score),0)),"") }

For H6 (2nd place), I have the following formula:

{ =IFERROR(INDEX(Name,MATCH(1,(H2=Division)*(H3=Subdivision)*(LARGE(IF(Division=H2,IF(Subdivision=H3,Score)),2)=Score),0)),"") }

What's happening with these formulas:

Each test in the Lookup Array argument of the MATCH statement returns an array of TRUE/FALSE values. When multiplied together, the array becomes a list of 0s and 1s. So the lookup array in the H5 MATCH formulas is:

{ 0;0;0;0;0;1;0;0;0;0 }

The MATCH matches 1 to the 6th place, returns a 6, thus returns the 6th value from the INDEX.

The MAX and LARGE functions show how you can use array formulas to achieve MAXIFS (or LARGE based on 2+ criteria). See this link for more info: https://exceljet.net/formula/maximum-if-multiple-criteria

Hope that helps!

 

[Eric W]

OK, a few quick comments. First, with the additional criteria, it might be best to rearrange your sheet. Indystick's layout is probably superior to the version I first posted. But it's up to you, if you can show us how you'd like the output to look, names and/or scores, we can work toward that.

Next, Indystick is also correct in that using Named ranges makes the formulas easier to read. He didn't explain how to do it though. The easiest way is to select the range, then type the name you want in the address box (left of the formula box). If need be, we can get fancier and create dynamic ranges, that automatically resize themselves as you add data - or even use a table. Both easy to do, but not used as much as they could be.

Finally, he takes a different approach which is pretty good. It shows the basic idea how to combine multiple conditions, by using the * as a Boolean "and". However, I'd probably replace the ",2" with ROWS($H$4:$H4) so that we can use the same formula and copy it down. The other problem is that if there's a tie for first, the H6 formula will show the same name as H5.

 

[Indystick]

I completely agree with Eric's comments, particularly about using the ROWS (or COLUMNS function if you're copying the formula horizontally) to dynamically determine the place you want to return. I am not sure how the duplicate value issue (e.g., tie for first) could be resolved. Eric, is that what you're addressing with the COUNTIF statements?