Return the tied values

[pchatziko]

I have some elements that i am trying to rank by a quantity then sorting them but they can be ties. I want to return an array giving me all the tied elements. The elements list will always be 4. I'm showing you some examples and the expected returns in the following minisheet. Thanks in advance

nfl (1).xlsm
	F	G	H	I	J	K
1	elements	quantity	rank quantity	sort	Sortby quantity	Expected return
2	A	3	1	1	A	A
3	B	3	1	1	B	B
4	D	1	4	3	C
5	C	2	3	4	D
6
7	elements	quantity	rank quantity	sort	Sortby quantity	Expected return
8	A	3	1	1	A	A
9	B	3	1	1	B	B
10	D	3	1	1	D	D
11	C	2	3	3	C
12
13
14	elements	quantity	rank quantity	sort	Sortby quantity	Expected return
15	A	2	3	1	B	B
16	B	3	1	1	D	D
17	D	3	1	3	A	A
18	C	2	3	3	C	C
19
20	elements	quantity	rank quantity	sort	Sortby quantity	Expected return
21	A	2	3	1	B	A
22	B	3	1	3	A	D
23	D	2	3	3	D	C
24	C	2	3	3	C
25
Sheet3

Cell Formulas
Range	Formula
I2:I5,I21:I24,I15:I18,I8:I11	I2	=SORT(H2:H5)
J2:J5,J21:J24,J15:J18,J8:J11	J2	=SORTBY(F2:F5,H2:H5)
H2:H5,H21:H24,H15:H18,H8:H11	H2	=RANK(G2,$G$2:$G$5)
Dynamic array formulas.

 

[KRice]

Here is one idea:

MrExcel_20220505B.xlsx
	F	G	H	I	J	K
1	elements	quantity	rank quantity	sort	Sortby quantity	Expected return
2	A	3	1	1	A	A
3	B	3	1	1	B	B
4	D	1	4	3	C
5	C	2	3	4	D
6
7	elements	quantity	rank quantity	sort	Sortby quantity	Expected return
8	A	3	1	1	A	A
9	B	3	1	1	B	B
10	D	3	1	1	D	D
11	C	2	4	4	C
12
13	elements	quantity	rank quantity	sort	Sortby quantity	Expected return
14	A	2	3	1	B	B
15	B	3	1	1	D	D
16	D	3	1	3	A	A
17	C	2	3	3	C	C
18
19	elements	quantity	rank quantity	sort	Sortby quantity	Expected return
20	A	2	2	1	B	A
21	B	3	1	2	A	D
22	D	2	2	2	D	C
23	C	2	2	2	C
Sheet4

Cell Formulas
Range	Formula
I2:I5,I20:I23,I14:I17,I8:I11	I2	=SORT(H2:H5)
J2:J5,J20:J23,J14:J17,J8:J11	J2	=SORTBY(F2:F5,H2:H5)
K2:K3,K20:K22,K14:K17,K8:K10	K2	=INDEX(SORT(FILTER(F2:H5,COUNTIF(H2:H5,H2:H5)>1),3),,1)
H2:H5	H2	=RANK(G2,G$2:G$5)
H8:H11	H8	=RANK(G8,G$8:G$11)
H14:H17	H14	=RANK(G14,$G$14:$G$17)
H20:H23	H20	=RANK(G20,$G$20:$G$23)
Dynamic array formulas.

 

[pchatziko]

It works some times some others not and i suspect it has to be because my elements array is not static but continuously changing therefore i get a spill error range unknown

 

[KRice]

Did you change the formulas in column H? Notice the difference between your original post and mine. In your original post, each element in every group of four was being ranked against the same set of data...the first set--the $G$5 in =RANK(G2,$G$2:$G$5, which isn't correct. In my post, I adjusted those ranges to refer to the current set of four.

 

[pchatziko]

No i'm afraid it wasn't the problem, i noticed if there is no tie the formula returns #Calc error so i added an iferror to get rid of it and i'm still getting "random" spill errors. The error at the #spill is unknown range which means the problem is the dynamic array i am using which has a root with rand() but watching it to work sometimes and sometimes not it sure is puzzling. Attaching the minisheet for more info.

nfl (1).xlsm
	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
2		AFC South	Wins	TIES	Wins + Ties	Rank		Sort Rank		Find ties
3	1	Indianapolis Colts	5	0	5	4	Houston Texans	1		#SPILL!
4	2	Tennessee Titans	6	0	6	3	Jacksonville Jaguars	2
5	3	Jacksonville Jaguars	9	0	9	2	Tennessee Titans	3
6	4	Houston Texans	11	0	11	1	Indianapolis Colts	4
7		AFC West
8	1	Kansas City Chiefs	9	0	9	2	Los Angeles Chargers	1		#SPILL!
9	2	Los Angeles Chargers	10	0	10	1	Kansas City Chiefs	2
10	3	Denver Broncos	6	0	6	4	Las Vegas Raiders	2
11	4	Las Vegas Raiders	9	0	9	2	Denver Broncos	4
12		AFC North
13	1	Baltimore Ravens	10	0	10	1	Baltimore Ravens	1		#SPILL!
14	2	Cleveland Browns	10	0	10	1	Cleveland Browns	1
15	3	Pittsburgh Steelers	8	0	8	3	Pittsburgh Steelers	3
16	4	Cincinnati Bengals	6	0	6	4	Cincinnati Bengals	4
17		AFC East
18	1	Buffalo Bills	8	0	8	3	New York Jets	1		#SPILL!
19	2	New England Patriots	10	0	10	2	New England Patriots	2
20	3	Miami Dolphins	7	0	7	4	Buffalo Bills	3
21	4	New York Jets	11	0	11	1	Miami Dolphins	4
22		NFC South
23	1	Tampa Bay Buccaneers	7	0	7	2	Atlanta Falcons	1		New Orleans Saints
24	2	New Orleans Saints	5	0	5	3	Tampa Bay Buccaneers	2		Carolina Panthers
25	3	Atlanta Falcons	10	0	10	1	New Orleans Saints	3
26	4	Carolina Panthers	5	0	5	3	Carolina Panthers	3
Schedule

Cell Formulas
Range	Formula
AH23:AH26,AH18:AH21,AH13:AH16,AH8:AH11,AH3:AH6	AH3	=COUNTIFS($D$3:$D$274,AG3,$H$3:$H$274,"H")+COUNTIFS($B$3:$B$274,AG3,$H$3:$H$274,"A")
AI23:AI26,AI18:AI21,AI13:AI16,AI8:AI11,AI3:AI6	AI3	=COUNTIFS($D$3:$D$274,AG3,$H$3:$H$274,"T")+COUNTIFS($B$3:$B$274,AG3,$H$3:$H$274,"T")
AJ23:AJ26,AJ18:AJ21,AJ13:AJ16,AJ8:AJ11,AJ3:AJ6	AJ3	=AH3+AI3*0.5
AK3:AK6	AK3	=RANK(AJ3,$AJ$3:$AJ$6)
AL3:AL6,AL23:AL26,AL18:AL21,AL13:AL16,AL8:AL11	AL3	=SORTBY(AG3:AG6,AK3:AK6)
AM3:AM6,AM23:AM26,AM18:AM21,AM13:AM16,AM8:AM11	AM3	=SORT(AK3:AK6)
AO3,AO8	AO3	=IFERROR(INDEX(SORT(FILTER(AL3:AM6,COUNTIF(AM3:AM6,AM3:AM6)>1),2),,1),"")
AK8:AK11	AK8	=RANK(AJ8,$AJ$8:$AJ$11)
AK13:AK16	AK13	=RANK(AJ13,$AJ$13:$AJ$16)
AO13,AO23:AO24,AO18	AO13	=IFERROR(INDEX(SORT(FILTER(AL13:AM16,COUNTIF(AM13#,AM13#)>1),2),,1),"")
AK18:AK21	AK18	=RANK(AJ18,$AJ$18:$AJ$21)
AK23:AK26	AK23	=RANK(AJ23,$AJ$23:$AJ$26)
Dynamic array formulas.

 

[KRice]

I think you're correct. I didn't realize the Wins and Ties values were coming from cells tied to volatile random numbers. I don't understand why this leads to the issue, as the "display only tied teams" formula (in the earlier post) works sometimes...but not all of the time. I couldn't work with the mini-sheet in your last post, as it references a lot of cells in columns B & D, which is where the volatile values are coming from. To simulate that, I added some random variation to the scores in the example here (keeping the variations small to produce a lot of tied rankings for troubleshooting). I tried more approaches than I can count in an attempt to find something that would reliably list teams with tied rankings. The only formula based approach that seemed to work reliably uses TEXTJOIN. The downside is this displays all tied teams in the same cell, separated by the delimiter of your choice (I've used a comma space here). I tried operating on the original team and rankings columns so as to reduce the extent of any FILTER/SORT spilling effects. The orange cell with TEXTJOIN works well. You'll see that it uses a SORT(FILTER(FILTER construction, and then joins the results in a TEXTJOIN function. Oddly, the same construction without the TEXTJOIN (yellow cell) does not work reliably. I also tried splitting the text in the orange cell to produce a list, but that too suffers from the #SPILL! issue. If the single cell listing is not satisfactory, I think VBA may offer a solution.

nfl2.xlsx
	AJ	AK	AL	AM	AN	AO			AR	AS	AT	AU	AV
12		AFC North	Wins	TIES	Wins + Ties	Rank				Sort Rank	Find ties
13	1	Baltimore Ravens	7	0	7	4			Cincinnati Bengals	1	Cleveland Browns, Pittsburgh Steelers	#SPILL!
14	2	Cleveland Browns	8	0	8	2			Cleveland Browns	2
15	3	Pittsburgh Steelers	8	0	8	2			Pittsburgh Steelers	2
16	4	Cincinnati Bengals	9	0	9	1			Baltimore Ravens	4
NFL

Cell Formulas
Range	Formula
AL13:AL16	AL13	=IF(ISNUMBER($AJ13),RANDBETWEEN(7,9),"")
AM13:AM16	AM13	=IF(ISNUMBER($AJ13),RANDBETWEEN(0,0),"")
AN13:AN16	AN13	=AL13+AM13*0.5
AO13:AO16	AO13	=RANK(AN13,$AN$13:$AN$16)
AR13:AS16	AR13	=FILTER(SORT(AK13:AO16,5),{1,0,0,0,1})
AT13	AT13	=LET( sties, SORT(FILTER(FILTER(AK13:AO16,COUNTIF(AO13:AO16,AO13:AO16)>1,""),{1,0,0,0,1}),2), TEXTJOIN(", ",TRUE,INDEX(sties,,1)))
AU13	AU13	=SORT(FILTER(FILTER(AK13:AO16,COUNTIF(AO13:AO16,AO13:AO16)>1,""),{1,0,0,0,1}),2)
Dynamic array formulas.

 

[pchatziko]

Yes i don't see any other way other than vba. Thanks for the feedback