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.

 

[erutherford]

Quick question. Indy are the formulas you provided (H5 & H6) can they go anywhere on the worksheet? If not, shouldn't they be H4 & H5 to correspond with the descriptions in G4 G5?

Right now I just trying to get the functions to work as in the examples. just takes me a bit longer than most.

 

[Indystick]

They can go anywhere in the worksheet. I pointed to the wrong cell locations in the post (should be H4 & H5) as you point out.

One other benefit of naming your ranges (or individual cells) is that you can reference them across worksheets without having to tab back and forth. For example, since I named A2:A11 "Name" I can reference "Name" on Sheet2 just by "Name" without having to include the Sheet2! prefix.

 

[erutherford]

First thanks for the suggestions. Naming ranges makes things much easier to read.

I used the code you provided and it works flawless. Wanted to add a few more pieces of data, so I changed "name" in your code to "entry". It is identical in structure, works perfect in my test sheet, but will not work in the final product. I have looked at the code for an hour, the structure is the same, but will not return the correct data. I have checked everything, data is there, spelling, etc. its a mirror image!

I must be missing something, troubleshooting suggestion?

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

 

[Eric W]

Indy, yes, I was using the COUNTIF to avoid duplicates. There are a couple ways to do it, starting from your formula, probably the easiest way is like this:

Excel 2012

	A	B	C	D	E	F	G	H
Name	Division	Subdivision	Score	Division:	Avenue
Bob	Full	A	Subdivision:	C
Joe	Street	B
Bill	Full	B	1st:	Mark
Ted	Street	A	2nd:	Alice
Al	Full	A
Mark	Avenue	C
Jake	Avenue	B
Mary	Avenue	A
Alice	Avenue	C
Xavier	Full	A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]

[TD="align: right"]299[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]298[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]297[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]296[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]261[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]251[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]260[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]261[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]299[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]{=IFERROR(INDEX(Name,MATCH(1,($H$1=Division)*($H$2=Subdivision)*(COUNTIF($H$3:$H3,Name)=0)*(LARGE(IF(Division=$H$1,IF(Subdivision=$H$2,Score)),ROWS($H$4:$H4))=Score),0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Division[/TH]
[TD="align: left"]=Sheet1!$B$2:$B$15[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Name[/TH]
[TD="align: left"]=Sheet1!$A$2:$A$15[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Score[/TH]
[TD="align: left"]=Sheet1!$D$2:$D$15[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Subdivision[/TH]
[TD="align: left"]=Sheet1!$C$2:$C$15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



You can see that I added one additional term, which checks to see if the name has been used before, and if it's not (the COUNTIF finds no match above the current row), then that term equals 1.


erutherford: it's very hard to figure out what's wrong with your formula without seeing it. The first thing that comes to mind though is to make sure that all your named ranges are the same size. Go to Formulas > Name Manager > and check that the start and end rows are the same for all of them.

Hope this helps!

 

[Indystick]

First thanks for the suggestions. Naming ranges makes things much easier to read.

I used the code you provided and it works flawless. Wanted to add a few more pieces of data, so I changed "name" in your code to "entry". It is identical in structure, works perfect in my test sheet, but will not work in the final product. I have looked at the code for an hour, the structure is the same, but will not return the correct data. I have checked everything, data is there, spelling, etc. its a mirror image!

I must be missing something, troubleshooting suggestion?

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

Did you create the named range "entry" (or change the existing named range name from 'name' to 'entry')?

 

[Indystick]

Just to make sure I understand correctly what is happening: if a name has already been listed (say for 1st place) it won't get listed again in 2nd place?

 

[erutherford]

Indy,
I finally got the formula to work. The formula wasn't the problem and I am not 100% sure what was. I cleared all the cells on the worksheet up to the last line of data and that did it. So I am guessing something was in a cell that affected the statement, I just didn't see it. It is working as it should and I have been able add more AND I found where you manage the cell ranges! I suspect that was part of the issue too? You both have added to my knowledge a lot, so thanks for that!