Return a 1st, 2nd, or 3rd from row of numbers.

[johnogolf]

Hi all, any help would be great, i have a column of points awarded in golf scoring sheet, 100,80,70,60,50,40, and so on, I want the 100 to be 1st, 80 to be 2nd, 70 to be 3rd, but to return the name of the person in column next to that particular score, ie, Joe smith gets 100 points so he will be first and to show up on another page in workbook.Only his name need show up, any help will be great thanks Johno.

[mrice]

Maybe something like

=INDEX(Sheet1!A:A,MATCH(A1,Sheet1!B:B,0))

where the Golfer name is in Sheet1 column A and the score in column B. On the second sheet, the score is in column B

[martindwilson]

as long as there always different with no ties something like
=VLOOKUP(LARGE(Sheet1!D1:D100,1),Sheet1!D1:H100,2,FALSE)
=VLOOKUP(LARGE(Sheet1!D1:D100,2),Sheet1!D1:H100,2,FALSE)
=VLOOKUP(LARGE(Sheet1!D1:D100,3),Sheet1!D1:H100,2,FALSE)
will suffice
or just=VLOOKUP(LARGE(Sheet1!$D$1:$D$100,rows($a$1:a1),Sheet1!$D$1:$H$100,2,FALSE) filled down for 3 further rows

[Jacc]

A little late...

[PietBom]

This solution also handles ties...
It is done with array-formulas

[johnogolf]

society scoring system template.xlsx Hey Guys, many thanks for your efforts, but as yet cant manage it so attached the file so maybe you can help? kind regards Johno

[martindwilson]

cant open that ah now i have,where exactly do you want the results to be and taken from where?

[johnogolf]

Wow your quick of the mark martin!! umm anywhere really but if you showed it say on the Stratford page i can figure it out from there, what i need is to produce a 1st 2nd and 3rd name from the points column, the points are filled in by hand as there is often ties and then its done on count back, just easier than trying to use formula. The resulting little picture/table of the 1,2,3rd places can be uploaded to our site, to go on the mobile section, ie phone app, Grateful for the help Johno

[johnogolf]

Hi Martin, any luck on sorting my problem, Johno

[martindwilson]

here you go based on results in col g i used pietboms array idea

[johnogolf]

Your "A STAR" Martin, Cant thank you enough, will now make it look pretty then upload to mobile app, in the summer if we get one this year, once again thanks! Johno

[newdoverman]

I took a guess that you are wanting formulae for the Events tab. I have created some samples for you on that tab.

[newdoverman]

I have revised the file on the events tab to use the list that is in column A. If these match the names of the Tabs then the formulae will use that listing to retrieve values from the sheet named.

[johnogolf]

Hey newdoverman, that's a cool bit of coding there, brilliant, i'll try and apply it to the other pages now, but huge thanks for your efforts, just love this forum and the help you people give us all, regards Johno

[newdoverman]

I discovered a problem with what I uploaded to you. What I gave you works for most but not all sheets. There is a problem on those sheets that I can't find that causes the calculations involving those sheets to fail.

I have though used a work around and it does work for all worksheets though in a different manner than what I have previously uploaded to you. I also used IFERROR to eliminate a bunch of error messages where data hasn't been entered.

I make use of named ranges extensively in complicated workbooks. In this workbook, I have shaded the named ranges which will appear blank until data is entered for the players. These ranges also have array formulae in them.

I also discovered that on the first sheet,the Stratford tab was ignored even though there were two columns in the order of merrit sheet that didn't have formulae. I entered formula addressing the stratford sheet. At the same time I revised the formulae in the other columns that didn't need to use the SUM function. All they required was a reference to the cells on the other sheets to retrieve the values.

I have done as much testing as I can to see that what I have given you actually works (as I think you want)

I hope that I haven't annoyed you with all this as I get a little involved at times.

[johnogolf]

Hey Newdoverman, Firstly i never get annoyed at someone trying to help me LOL, but excel is a bit like that eh? getting you involved with it i mean, i love it, not as clever as you with it for sure but i do enjoy using it. Reference the comment you made about the stratford tab, the reason its not linked was the committee decided in their wisdom it was the first meeting of the year and will not count in the order of merit points , hence not linked,I left it in case needed or times change. As yet i haven't been able to download it, the site is having trouble with server i believe? O revision just downloaded! I'll drop you another line after I've looked at it but for now many thanks Johno

[johnogolf]

Hi newdoverman, yes all seems to work perfect, but the only prob i can see for the future is next year as the venues change, i assume the coding will be affected by that to? regards Johno

[newdoverman]

The coding will likely have to change but the same formulae on the location sheets can be used, just change the names of the named ranges under the Data tab,Name Manager. Change the names in the Name Manager to match your new data and all the calculations that used the old names will now use the new names. It will take just a couple of minutes to do this.

[johnogolf]

Hi Newdoverman, just a big thanks for all your help, regards Johno

[glynncooper]

hi martin do you have a blank template of (society scoring system template) so i can add my own players and venues E.T.C

thanks

glynn