Results by handicap - with ties. Look up by score without duplicates

[dkjonesau]

Hi all,

I've got a table of results broken up into 9 divisions/handicap tiers.

I have a second worksheet that shows places 1st - 4th in each division. But I'm struggling with my name display when there's tied scores.

See workbook linked from OneDrive - Sample workbook

In the second worksheet "Results" I need to show positions 1st to 4th. I've worked out getting the scores out of each division for the top 4 scores.

I can't work out the best way to handle the duplicates.

In the sample worksheet Column M in the DATA PASTE HERE sheet allocates the division & column P is the score. Column B is the entrant's name.

Where there's two identical scores in a division I need to move on to the next name, and only in that division. I'm only using 4th to check if there's a tie for third place. I'll go to a manual process if I have to at that point. If it's less than third's score I don't have to do anything.

Data table above is summarised here for a display of who's winning. But I need to remove duplicates within the same division and across divisions.

Any suggestions welcome.

Thanks

Dave

 

[Cubist]

Maybe this.

Book1
	D	E	F	G	H	I	J	K	L	M	N
6	Division	1	OTG	2	OTG	3	OTG	4	OTG	5	OTG
7	1st	Random Name3	69	Random Name10	65	Random Name20	68	Random Name42	69	Random Name52	59
8	2nd	Random Name2	67	Random Name12	64	Random Name22	65	Random Name45	67	Random Name49	58
9	3rd	Random Name8	67	Random Name16	64	Random Name34	64	Random Name36	63	Random Name50	57
10	4th	Random Name7	66	Random Name11	62	Random Name27	63	Random Name37	63	Random Name55	57
11
12	Division	6	OTG	7	OTG	8	OTG	9	OTG
13	1st	Random Name63	58	Random Name68	44	Random Name69	52	Random Name73	22
14	2nd	Random Name64	56	Random Name67	40	Random Name70	52		#NUM!
15	3rd	Random Name66	54		#NUM!	Random Name71	52		#NUM!
16	4th	Random Name62	50		#NUM!	Random Name72	27		#NUM!
Sheet5

Cell Formulas
Range	Formula
E7:E10,K13,I13:I16,G13:G14,E13:E16,M7:M10,K7:K10,I7:I10,G7:G10	E7	=TAKE(LET(s,SORTBY(All[Name],All[Ev.1 M/FT],1,All[Ev.1 OTG],-1),FILTER(s,All[Ev.1 M/FT]=E6)),4)
Dynamic array formulas.

 

[dkjonesau]

Maybe this.

Book1
	D	E	F	G	H	I	J	K	L	M	N
6	Division	1	OTG	2	OTG	3	OTG	4	OTG	5	OTG
7	1st	Random Name3	69	Random Name10	65	Random Name20	68	Random Name42	69	Random Name52	59
8	2nd	Random Name2	67	Random Name12	64	Random Name22	65	Random Name45	67	Random Name49	58
9	3rd	Random Name8	67	Random Name16	64	Random Name34	64	Random Name36	63	Random Name50	57
10	4th	Random Name7	66	Random Name11	62	Random Name27	63	Random Name37	63	Random Name55	57
11
12	Division	6	OTG	7	OTG	8	OTG	9	OTG
13	1st	Random Name63	58	Random Name68	44	Random Name69	52	Random Name73	22
14	2nd	Random Name64	56	Random Name67	40	Random Name70	52		#NUM!
15	3rd	Random Name66	54		#NUM!	Random Name71	52		#NUM!
16	4th	Random Name62	50		#NUM!	Random Name72	27		#NUM!
Sheet5

Cell Formulas
Range	Formula
E7:E10,K13,I13:I16,G13:G14,E13:E16,M7:M10,K7:K10,I7:I10,G7:G10	E7	=TAKE(LET(s,SORTBY(All[Name],All[Ev.1 M/FT],1,All[Ev.1 OTG],-1),FILTER(s,All[Ev.1 M/FT]=E6)),4)
Dynamic array formulas.

Hi Cubist,

That looks like it's going to do the job based on your post but if I enter your formula into my E7 from your E7 it doesn't like it.

If I do a simple paste & ENTER it outputs this error which is weird cos it's clearly a formula not text. I've had this once before pasting from forums/internet but I can't recall how I got around it.

If I change it to this by replacing your table info by clicking on the top of the corresponding table column in the other sheet it doesn't error and accepts the formula but it does come up with a #CALC! error (Empty array?) in E7. I recalled having to Ctrl-Shift-ENTER for array formulae from years ago but I don't have much experience with arrays.

=TAKE(LET(s,SORTBY(Table1[[#Headers],[Name]],Table1[[#Headers],[Ev.1 M/FT]],1,Table1[[#Headers],[Ev.1 OTG]],-1),FILTER(s,Table1[[#Headers],[Ev.1 M/FT]]=E6)),4)

Thanks for your assistance. Thoughts? Where have I gone wrong.

Dave

 

[Cubist]

It looks like you're grabbing the headers of the table. Only get the data itself (row 2 and below in your sample sheet).

 

[dkjonesau]

It looks like you're grabbing the headers of the table. Only get the data itself (row 2 and below in your sample sheet).

=TAKE(LET(s,SORTBY(Table1[Name],Table1[Ev.1 M/FT],1,Table1[Ev.1 OTG],-1),FILTER(s,Table1[Ev.1 M/FT]=E6)),4)

Just had to swap out All in your formula for my Table1 table name. Knew it had to be something simple, just had me scratching my head for a minute. Nice, I've not used anything like that before but it's very neat. I'll have a play in another application too!

Thanks Cubist. Much appreciated.

Dave