FILTER multiple criteria

[macfuller]

In my NCAA pool we place points per team, with each player winning that number of points each time that team wins. Standings are as shown.

	Player	Points	Max Remaining Points
1	Player 4	192	28
2	Player 7	190	29
3		189	29
4		188	29
5		187	29
6		186	29
7		185	29
8		184	29
9		183	28

What I would like to do is show next to each player the top x teams s/he still has in the running. So for example Player 4 could show Auburn 10, Florida 9, Houston 7 in the next cell as his top picks, having put 10 on Auburn etc.

A player's picks are stored in the following table. So Player 3's cell opposite the standings would show Florida 10, Houston 9, Auburn 8 (this being what's left in the Final Four).

Teams	Total Pts	Pick to Win	Player 1	Player 2	Player 3
(01) Auburn	196	3	6	7	8
(01) Duke	200	11	8	10
(01) Florida	155	4	5	8	10
(01) Houston	165	4	10	9	9
(02) Alabama	114			5	2
(02) Michigan St	131	1	9	3	1
(02) St. Johns	88	1	4	6	6
(02) Tennessee	89		7	2
(03) Iowa State	18				3
(03) Kentucky	28				5
(03) Texas Tech	48	1
(03) Wisconsin	27			4	7
(04) Arizona	30				4
(04) Maryland	14		1
(04) Purdue	5
(04) Texas A&M	1
(05) Clemson	9
(05) Memphis	4
(05) Michigan	6
(05) Oregon	9			1
(06) BYU	3		3
(06) Illinois	4
(07) Kansas	17
(07) UCLA	6
(08) Gonzaga	6		2
(11) Drake	2

I have a named range called LostList that tells whether the team has been knocked out of the tournament. It matches the order of the teams in the pick list above.
...
(01) Houston FALSE
(02) Alabama TRUE
...
etc.

So I would like a TRANSPOSE(FILTER(LARGE(...))) construction that would take the top x teams by player ranking for each player and show it in the cell next to the standings.

Any ideas? Thanks.

 

[Peter_SSs]

I'm not entirely clear what you want but would this be headed in the right direction?

25 04 02.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Player	Points	Max Remaining Points			Teams	Total Pts	Pick to Win	Player 1	Player 2	Player 3
2	Player 3	192	28	(01) Florida 10, (01) Houston 9, (01) Auburn 8		(01) Auburn	196	3	6	7	8
3		190	29			(01) Duke	200	11	8	10
4		189	29			(01) Florida	155	4	5	8	10
5		188	29			(01) Houston	165	4	10	9	9
6		187	29			(02) Alabama	114			5	2
7		186	29			(02) Michigan St	131	1	9	3	1
8		185	29			(02) St. Johns	88	1	4	6	6
9		184	29			(02) Tennessee	89		7	2
10		183	28			(03) Iowa State	18				3
11						(03) Kentucky	28				5
12						(03) Texas Tech	48	1
13						(03) Wisconsin	27			4	7
14						(04) Arizona	30				4
15						(04) Maryland	14		1
16						(04) Purdue	5
17						(04) Texas A&M	1
18						(05) Clemson	9
19						(05) Memphis	4
20						(05) Michigan	6
21						(05) Oregon	9			1
22						(06) BYU	3		3
23						(06) Illinois	4
24						(07) Kansas	17
25						(07) UCLA	6
26						(08) Gonzaga	6		2
27						(11) Drake	2
macfuller

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN({" ",", "},,TAKE(SORT(CHOOSECOLS(G2:L27,1,XMATCH(B2,G1:L1)),2,-1),3))

 

[macfuller]

Yes it is a step in the right direction. I'll have to dig into this further, but I'll check out the link to whether the top team is still playing (e.g. if I put 10 points on MSU but they didn't make it to the final 4).

I can do this above each column for the picks table (tblPicks) where one of the player's columns is "Powers C" and the reference of whether the team is still in the tournament is the Team_Still_Playing array. But I'd like something more elegant along your lines.

=LET(
Topt1, LARGE(tblPicks[Powers C] * Team_Still_Playing,1),
Topt2, LARGE(tblPicks[Powers C] * Team_Still_Playing,2),
Topt3, LARGE(tblPicks[Powers C] * Team_Still_Playing,3),
TopN1, IF(Topt1 > 0, XLOOKUP(Topt1,tblPicks[Powers C],tblPicks[[Teams]:[Teams]]) & " " & Topt1 & "pts", ""),
TopN2, IF(Topt2 > 0, ", " & XLOOKUP(Topt2,tblPicks[Powers C],tblPicks[[Teams]:[Teams]]) & " " & Topt2 & "pts", ""),
TopN3, IF(Topt3 > 0, ", " & XLOOKUP(Topt3,tblPicks[Powers C],tblPicks[[Teams]:[Teams]]) & " " & Topt3 & "pts", ""),
TopN1 & TopN2 & TopN3
)

This is the sample of the Team_Still_Playing array. The teams are in the same order in tblPicks and in the array so the "tblPicks[Powers C] * Team_Still_Playing" logic works.

Team Still Playing	Teams
TRUE	(01) Auburn
TRUE	(01) Duke
TRUE	(01) Florida
TRUE	(01) Houston
FALSE	(02) Alabama
FALSE	(02) Michigan St
FALSE	(02) St. Johns
FALSE	(02) Tennessee
FALSE	(03) Iowa State
FALSE	(03) Kentucky
FALSE	(03) Texas Tech
FALSE	(03) Wisconsin
FALSE	(04) Arizona
FALSE	(04) Maryland
FALSE	(04) Purdue
FALSE	(04) Texas A&M

 

[Peter_SSs]

See if this is any better. If still not what you want, further explanation needs to focus on the data in the worksheet(s), where it is, what should be returned etc. I don't know anything about the sport, competition, what NCAA or MSU are etc - but I don't need to know that, just what is required from Excel.

I have changed the 'Team Still Playing' sample data a bit for testing purposes. Hopefully I have the table structure roughly correct.

If further help needed it might also help if you investigated XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. Also easy to copy for testing.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

macfuller.xlsm
	B			E	F	G	H	I	J	K	L	M
1	Player					Team Still Playing	Teams	Total Pts	Pick to Win	Player 1	Player 2	Player 3
2	Player 3			(01) Florida 10, (01) Auburn 8, (02) St. Johns 6		TRUE	(01) Auburn	196	3	6	7	8
3						TRUE	(01) Duke	200	11	8	10
4						TRUE	(01) Florida	155	4	5	8	10
5						FALSE	(01) Houston	165	4	10	9	9
6						FALSE	(02) Alabama	114			5	2
7						FALSE	(02) Michigan St	131	1	9	3	1
8						TRUE	(02) St. Johns	88	1	4	6	6
9						FALSE	(02) Tennessee	89		7	2
10						FALSE	(03) Iowa State	18				3
11						FALSE	(03) Kentucky	28				5
12						FALSE	(03) Texas Tech	48	1
13						FALSE	(03) Wisconsin	27			4	7
14						FALSE	(04) Arizona	30				4
15						FALSE	(04) Maryland	14		1
16						FALSE	(04) Purdue	5
17						FALSE	(04) Texas A&M	1
18						FALSE	(05) Clemson	9
19						FALSE	(05) Memphis	4
20						FALSE	(05) Michigan	6
21						FALSE	(05) Oregon	9			1
22						FALSE	(06) BYU	3		3
23						FALSE	(06) Illinois	4
24						FALSE	(07) Kansas	17
25						FALSE	(07) UCLA	6
26						FALSE	(08) Gonzaga	6		2
27						FALSE	(11) Drake	2
28
macfuller

Cell Formulas
Range	Formula
E2	E2	=TEXTJOIN({" ",", "},,TAKE(SORT(CHOOSECOLS(FILTER(tblPicks,tblPicks[Team Still Playing]),2,XMATCH(B2,tblPicks[#Headers])),2,-1),3))

 

[macfuller]

I'm getting a #VALUE error when I tweak it, but I suspect I'm adapting it incorrectly. I have several sheets where relevant formulas are kept so xl2bb would be a bowl of spaghetti... happy to send you the spreadsheet!
I can use the LET statement in my previous post above in a recursive LAMBDA, but I'd love to understand the logic of your solution. I've never used TAKE for example, but it may be a more recent Office 365 syntax. I'm faltering at the intent of the FILTER statement for example. But the logic is worthy of the sadly-departed ExcelXOR website.
Of course the spreadsheet is legacy, built up over many years from at least Excel 2003 onward, so it would require a massive overhaul to do it "right".

I see you're in Australia. The sport is the USA division 1 national college basketball tournament, aka "March Madness", run by the governing college athletic body the NCAA. A significant portion of the (not entirely male) US population creates office pools using various scoring methods and platforms. TV networks have their own pools. Warren Buffet offered $1,000,000 one year for anyone with a perfect bracket (correctly guessing the outcome of all 63 games). (Play-ins didn't count, but we won't go into that. ) MSU is Michigan State University, one of many collegiate acronyms.

 

[Peter_SSs]

I'm getting a #VALUE error when I tweak it,

Without seeing the tweak(s) or the data, hard to offer any constructive comment. However, one guess is that this red 2 in the formula should be the column number in the table of the 'Teams' column (column 2 in my table but perhaps not in yours).
=TEXTJOIN({" ",", "},,TAKE(SORT(CHOOSECOLS(FILTER(tblPicks,tblPicks[Team Still Playing]),2,XMATCH(B2,tblPicks[#Headers])),2,-1),3))

Alternatively the formula could work out that number ..
=TEXTJOIN({" ",", "},,TAKE(SORT(CHOOSECOLS(FILTER(tblPicks,tblPicks[Team Still Playing]),XMATCH("Teams",tblPicks[#Headers]),XMATCH(B2,tblPicks[#Headers])),2,-1),3))

or

=LET(hdrs,tblPicks[#Headers],TEXTJOIN({" ",", "},,TAKE(SORT(CHOOSECOLS(FILTER(tblPicks,tblPicks[Team Still Playing]),XMATCH("Teams",hdrs),XMATCH(B2,hdrs)),2,-1),3)))

happy to send you the spreadsheet!

You cannot send it directly to me (or any other forum member) per the intent of #4 of the Forum Rules which is to ensure the public forum is public.

An option is to upload a sample workbook somewhere (eg DropBox, OneDrive, Google Drive etc) and provide a publicly shared link here.
Best if any sample file is kept simple and smallish because helpers here will not be familiar with your workbook and if it is too hard to follow then you probably won't get the help you are after.

I'm faltering at the intent of the FILTER statement for example.

The FILTER in my formula is immediately choosing only the rows of the table where 'Team Still Playing' is TRUE so we are only looking at rows of remaining teams.

 

[macfuller]

tblPicks[Teams] and tblPicks[Player x] are in one table. Teams_Still_Playing is a named ranged on a different sheet than tblPicks, though it has the same number of rows as tblPicks (though without the header row). Would I need HSTACK to join them before using CHOOSECOLS?

 

[Peter_SSs]

Would I need HSTACK to join them before using CHOOSECOLS?

No, provided both the table and the named range have the same number of rows and in the same team order you should be able to use ..

=LET(hdrs,tblPicks[#Headers],TEXTJOIN({" ",", "},,TAKE(SORT(CHOOSECOLS(FILTER(tblPicks,Teams_Still_Playing),XMATCH("Teams",hdrs),XMATCH(B2,hdrs)),2,-1),3)))