1st place, 2nd place winner

erutherford

Active Member
Joined
Dec 19, 2016
Messages
449
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.
 
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.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Last edited:
Upvote 0
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?

Code:
{ =IFERROR(INDEX(Name,MATCH(1,(H2=Division)*(H3=Subdivision)*(MAX(IF(Division=H2,IF(Subdivision=H3,Score)))=Score),0)),"") }
 
Upvote 0
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
ABCDEFGH
1NameDivisionSubdivisionScoreDivision:Avenue
2BobFullA299Subdivision:C
3JoeStreetB298
4BillFullB2971st:Mark
5TedStreetA2962nd:Alice
6AlFullA300
7MarkAvenueC261
8JakeAvenueB251
9MaryAvenueA260
10AliceAvenueC261
11XavierFullA299

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
H4{=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)),"")}

<thead>
</thead><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>

Workbook Defined Names
NameRefers To
Division=Sheet1!$B$2:$B$15
Name=Sheet1!$A$2:$A$15
Score=Sheet1!$D$2:$D$15
Subdivision=Sheet1!$C$2:$C$15

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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!
 
Upvote 0
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?

Code:
{ =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')?
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top