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:

I want to be able to have 2 stats columns in my other sheet:

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))))})))

-