CODEWITHSUNDEEP
google-sheets
field158
field158

Reputation: 111

How to find most frequent value in two columns based on a condition in Google Sheets?

I'm not too savvy with programming, so bear with me. The data is of the players' names and fighters in a fighting game, with the table as below: enter image description here

I'm making a leaderboard where it will show which fighter each player uses most. I found a formula that shows which fighter is used the most in general using the following formula:

=index(query({E2:E,G2:G},
"select Col1,count(Col1)
group by Col1
order by count(Col1) desc"), 2, 1)

The problem is, I don't understand how to make a formula that checks the player's name before returning the most used fighter from two columns. What I'm after is a result like so:

enter image description here

Thanks in advance!

Upvotes: 1

Views: 210

Answers (1)

Theza
Theza

Reputation: 613

Formula

=ArrayFormula(
{QUERY(QUERY({SPLIT("Wins"&"|"&UNIQUE({D2:D;F2:F}&"|"&"."),"|");SPLIT("Loss"&"|"&UNIQUE({D2:D;F2:F}&"|"&"."),"|");
              SPLIT("Wins"&"|"&FILTER(D2:D&"|"&E2:E,D2:D<>""),"|");
              SPLIT("Loss"&"|"&FILTER(F2:F&"|"&G2:G,F2:F<>""),"|")},
       "select Col2,count(Col3)-1 where Col2!='.' group by Col2 pivot Col1"),"select Col1,Col3,Col2,Col3/Col2,Col3/(Col3+Col2) order by Col3 desc label Col1 'Name',Col2 'Loss',Col3 'Wins',Col3/Col2 'W/L',Col3/(Col3+Col2) 'Win Rate %' format Col3/Col2 '0.0',Col3/(Col3+Col2) '#.0%'"),
VLOOKUP(INDEX(QUERY({D2:D},"select Col1,count(Col1) where Col1!='' group by Col1 order by count(Col1) desc label Col1 'Name'"),0,1),QUERY({D2:E;F2:G},"select Col1,Col2,count(Col2) group by Col1,Col2 order by Count(Col2) desc label Col1 'Name',Col2 'Most Used Character'"),2,0)})

enter image description here

Note:

column Wins/Loss cannot be calculated when Loss=0 .

Hope you're okay with that

Upvotes: 1

Related Questions