I'm trying to put some UNIQUE values from a COLUMN into a set of ROWS. In Excel I use ToRow, but I cannot find an equivalent in Google Sheets. Here was the origianl question I asked (and was answered), but the answer was related to Excel and now I've uploaded it to Google Sheets and it is not working:
I have an excel spreadsheet to track a game I play and I'm trying to determine how often I win with a certain character. There is some metadata in the table (like date, location, etc) but the important parts are this:
Character | Winner |
---|---|
Yuriko | me |
Yuriko | Derevi |
Yuriko | me |
Yuriko | me |
Winota | me |
Winota | Derevi |
I want to be able to have 2 stats columns in my other sheet:
Yuriko Win % | Winota Win % |
---|---|
75% | 50% |
I know how to find the win percentage, but I dont know how to limit my data set by character. Is this possible in Excel?
I'm also happy to use a chart here, if that makes it easier.
Edit: Not the cleanest solution, so if someone has something better, I'm all ears, but heres what I have:
Yuriko Games Played:
=COUNTIF('Game Log'!F2:'Game Log'!F500,"Yuriko")
Yuriko Wins:
=COUNTIFS('Game Log'!K2:K500,"=me",'Game Log'!F2:F500,"=Yuriko")
Then I just copy this for Winota too.
Now that I think about it, I'd love maybe a Stacked Chart that could stack the data more visually.
Solution (For Excel, not for Google Sheets):
Insert formula in A12 : =TOROW(UNIQUE(A2:A7,FALSE),0,TRUE)&" Win %"
It will spill all unique characters in row. Insert formula in A13: =TOROW(COUNTIFS(A2:A7,UNIQUE(A2:A7),B2:B7,"me")/COUNTIF(A2:A7,UNIQUE(A2:A7)))
it will spill win percentages in rows.
CodePudding user response:
To change columns into rows and vice versa you can use TRANSPOSE: =TRANSPOSE(UNIQUE(A2:A))
And for the percentages you can use BYCOL to iterate through those values:
=BYCOL(TRANSPOSE(UNIQUE(A2:A)),LAMBDA(each, IFERROR(COUNTIFS(A2:A,each,B2:B,"me")/COUNTIF(A2:A,each))))
CodePudding user response:
try this out in your sheets:
as shown in the screenshot, raw data is in columns A,B. I have the formula in cell D1
=TRANSPOSE(BYROW(UNIQUE(FILTER(A2:A,A2:A<>"")),LAMBDA(ax,{ax&" Win %",TO_PERCENT(COUNTA(IFERROR(FILTER(A:A,A:A=ax,B:B="me")))/COUNTA(IFERROR(FILTER(A:A,A:A=ax))))})))
-