Hello
i would like an automatic league table on the League tab ranking the player with the highest scores 1st, Using the data from Sheet 1 & 2
please see attached
Paul
Hello
i would like an automatic league table on the League tab ranking the player with the highest scores 1st, Using the data from Sheet 1 & 2
please see attached
Paul
You can do this easily enough by having a helper column (that can be hidden) to sum the points, then use the LARGE function for the ranking table.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Excel 2016 (Windows) 32 bit
A B C D E F G H I J 1POS Player Points Hidden Columns 21st Player 19 20Player 1 5 5.001Sheet1 32nd Player 16 15Player 2 10 10.001Sheet2 43rd Player 15 11Player 3 2 2.001 54th Player 2 10Player 4 9 9.001 65th Player 13 9Player 5 8 8.001 76th Player 4 9Player 6 6 6.001 87th Player 12 8Player 7 5 5.002 98th Player 5 8Player 8 4 4.001 109th Player 6 6Player 9 3 3.001 1110th Player 17 5Player 10 1 1.001 1211th Player 11 5Player 11 5 5.003 1312th Player 7 5Player 12 8 8.002 1413th Player 1 5Player 13 9 9.002 1514th Player 14 4Player 14 4 4.002 1615th Player 8 4Player 15 11 11.001 1716th Player 18 3Player 16 15 15.001 1817th Player 9 3Player 17 5 5.004 1918th Player 3 2Player 18 3 3.002 2019th Player 20 1Player 19 20 20.001 2120th Player 10 1Player 20 1 1.002
Sheet: League
Excel 2016 (Windows) 32 bit
F G H 2Player 1 =SUMPRODUCT(SUMIF(INDIRECT("'"&$J$2:$J$3&"'!A1:J1"),F2,INDIRECT("'"&$J$2:$J$3&"'!A2:J2"))) =G2+COUNTIF(G$2:G2,G2)*0.001
Sheet: League
Excel 2016 (Windows) 32 bit
B C 2=INDEX($F$2:$F$21,MATCH(LARGE($H$2:$H$21,ROWS(A$2:A2)),$H$2:$H$21,0)) =INDEX($G$2:$G$21,MATCH(LARGE($H$2:$H$21,ROWS(A$2:A2)),$H$2:$H$21,0))
Sheet: League
Last edited by AliGW; 08-10-2018 at 02:10 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
I have closed your duplicate thread as it was merely an extension of this one.
Having made a significant effort to help you, I would have expected at least a word or two of thanks. Please do not take for granted the free help you get here - remember, those who help you are volunteers doing so in their free time.
Post your new workbook here with the extended requirements.
hello Ali
i thought I'd marked the thread as solved and sent thanks, as you definitely solved my original post, Clearly i didn't do either,
however i think I've under estimated what I'm asking as i couldn't apply your help to my master sheet,
Stripped back master copy
i need a formula so i can quickly have a league table for points scored on a particular week
On the Week Score tab
league table of scores for each week using data on the A&B (Data highlighted in Blue)
week 1 sample data = yellow
Week 2 sample date = Orange
your ordinal solution was perfect for week one, but it wouldn't copy down (due to the offset maybe)
Paul
You marked the thread as solved, but as you needed to continue here, I removed the solved tag. In terms of thanks: if you mean rep points, then no, I received none from you, but to be honest I'm not bothered about them. I'm more interested in people at least having the courtesy to acknowledge another person's help in the thread where it is offered.
I will have a new look at your problem now.
OK - this is a lot more complicated than the over-simplified sample you gave originally. It's much easier for all concerned if you present realistic sample data in the first instance, and it saves time in the long run. I will have a look at this later when I have a bit more time, unless someone else wants to jump in and have a go.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks