League Table from 2 Sheets

[pmw19800]

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

[AliGW]

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.

[AliGW]

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J
1	POS	Player	Points			Hidden Columns
2	1st	Player 19	20			Player 1	5	5.001		Sheet1
3	2nd	Player 16	15			Player 2	10	10.001		Sheet2
4	3rd	Player 15	11			Player 3	2	2.001
5	4th	Player 2	10			Player 4	9	9.001
6	5th	Player 13	9			Player 5	8	8.001
7	6th	Player 4	9			Player 6	6	6.001
8	7th	Player 12	8			Player 7	5	5.002
9	8th	Player 5	8			Player 8	4	4.001
10	9th	Player 6	6			Player 9	3	3.001
11	10th	Player 17	5			Player 10	1	1.001
12	11th	Player 11	5			Player 11	5	5.003
13	12th	Player 7	5			Player 12	8	8.002
14	13th	Player 1	5			Player 13	9	9.002
15	14th	Player 14	4			Player 14	4	4.002
16	15th	Player 8	4			Player 15	11	11.001
17	16th	Player 18	3			Player 16	15	15.001
18	17th	Player 9	3			Player 17	5	5.004
19	18th	Player 3	2			Player 18	3	3.002
20	19th	Player 20	1			Player 19	20	20.001
21	20th	Player 10	1			Player 20	1	1.002

Sheet: League

Excel 2016 (Windows) 32 bit

	F	G	H
2	Player 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

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[AliGW]

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.

[pmw19800]

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

[AliGW]

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.

[AliGW]

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.