0

I am attempting to use the Google Sheets query function to grab from another sheet that looks kind of like this:

Spreadsheet

What I wish is for a list of the racer winners and how many times they have won a race. My actual sheet is much wider with repeats of tracks and I am trying to see who has won at which track and how many times.

I have tried something like this:

=query({'SheetName'!1:998}, "Select Col1 where (Col2 = 1) or (Col3 = 1) or (Col4 = 1) or (Col5 = 1)")

Which does give me the list of racers who won, but obviously doesn't tell me how many times they have won.

So I tried: =query({'SheetName'!1:998}, "Select Col1, Count(Col1) where (Col2 = 1) or (Col3 = 1) or (Col4 = 1) or (Col5 = 1) group by Col1")

but that just returns the same list with a 1 next to each name.

Any help would be appreciated thanks!

EDIT: Here is a link to a more realistic demo sheet Sheet Maker sample sheet

Below is an example of what I would like the output to be based on me, using the above sheet, for the winners of Track 1.

Racers: Wins:
Frank 1
Larry 1
Joe 2

Edit 2: I have also made a second tab a where I show the attempts I have tried in the past

LordSolarSteve
  • 3
  • 3

2 Answers2

0

Added one approach here for you to test out:

=let(rep_,A2:A, grp_,B2:AO, header_,B1:AO1, size_,1,
     Σ,reduce(wraprows(,columns(rep_)+1+size_,),sequence(columns(grp_)/size_,1,1,size_),lambda(a,c,{a;{rep_,wrapcols(index(header_,c),rows(rep_),index(header_,c)),choosecols(grp_,sequence(size_,1,c))}})),
     Λ,filter(Σ,index(Σ,,column(grp_)+1)<>""),
     query(Λ,"Select Col1,count(Col3) Where Col2 contains 'Finish' AND Col3=1 group by Col1 pivot Col2"))
  • Adjust the ranges in the first line of the formula as needed

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • I do not understand how you are getting the tracks to group? In my real data they might not be in repeating order and might have slightly different names but with the same base name like "Detroit 500" would be the same as "Detroit Grand Prix" for track win purposes but they were different races and in one season it could have been the first race, the next it was the 4th lets say. – LordSolarSteve Jul 15 '23 at 22:07
  • Was able to use this to get my excel sheet working thanks! – LordSolarSteve Jul 16 '23 at 01:28
0

Your data is in a "wide" format:

Racers Track 1 Start Track 1 Finish Track 2 Start Track 2 Finish Track 3 Start Track 3 Finish Track 4 Start Track 4 Finish Track 5 Start Track 5 Finish Track 1 Start Track 1 Finish Track 2 Start Track 2 Finish Track 3 Start Track 3 Finish Track 4 Start Track 4 Finish Track 5 Start Track 5 Finish Track 1 Start Track 1 Finish Track 2 Start Track 2 Finish Track 3 Start Track 3 Finish Track 4 Start Track 4 Finish Track 5 Start Track 5 Finish Track 1 Start Track 1 Finish Track 2 Start Track 2 Finish Track 3 Start Track 3 Finish Track 4 Start Track 4 Finish Track 5 Start Track 5 Finish
Joe 1 8 2 5 7 2 1 1 8 1 2 1 3 8 8 3 2 3 3 1 3 8
Frank 2 1 3 8 8 3 2 3 1 5 1 8 2 5 7 2 1 1 2 5
Mary 3 2 4 6 3 6 2 7 3 2 4 6 3 6 4 6
Sarah 4 3 5 7 4 2 4 3 5 7 4 2 8 8 1 7 5 7

One way to count how many wins each racer has in each track is to unpivot the data and then use query(), like this:

=let( 
  unpivot_, lambda(d,f,g,let(r_,lambda(t,o,h,chooserows(t,sequence(h,1,o))),c_,lambda(t,o,w,choosecols(t,sequence(1,w,o))),s_,lambda(r,c,h,w,r_(c_(d,c,w),r,h)),l_,lambda(c,s_(1,c,1,1)),hr,hstack(s_(1,1,1,f),"Track","Position"),i,sequence(1,(columns(d)-f)/g,f+1,g),n,tocol(iferror(ø),true),reduce(hr,sequence(rows(d)-1,1,2),lambda(z,r,let(e,s_(r,1,1,f),s,reduce(n,i,lambda(t,c,let(x,s_(r,c,1,g),if(single(x)="",t,vstack(t,hstack(e,l_(c),x)))))),vstack(z,s)))))), 

  unpivoted, unpivot_('Build Sample Here'!A1:AO, 1, 1), 

  query(unpivoted, "select Col1, count(Col1) where Col3 = 1 and Col2 ends with 'Finish' group by Col1 pivot Col2", 1) 
)

The formula unpivots the "wide" table into a "tall" table so that there are just three columns:

Racers Track Position
Joe Track 1 Start 1
Joe Track 1 Finish 8
Joe Track 2 Start 2
Joe Track 2 Finish 5
... ... ...
Frank Track 1 Start 2
Frank Track 1 Finish 1
Frank Track 2 Start 3
Frank Track 2 Finish 8
... ... ...

It then extracts names and counts separately for each "Finish" track using query() with a pivot clause:

Racers Track 1 Finish Track 2 Finish Track 3 Finish Track 4 Finish Track 5 Finish
Casey 1
Frank 1 1
Hank 1
Joe 2 1 1
Josh 1
Ken 2
Kristen 1
Kurt 1
Larry 1 1
Nancy 1
Neil 1
Parker 1

See How to unpivot data that has multiple column groups in Google Sheets and the sample spreadsheet.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • I do not understand how you are getting the tracks to group? In my real data they might not be in repeating order and might have slightly different names but with the same base name like "Detroit 500" would be the same as "Detroit Grand Prix" for track win purposes but they were different races and in one season it could have been the first race, the next it was the 4th lets say. – LordSolarSteve Jul 15 '23 at 22:07
  • The formula unpivots the "wide" table into a "tall" table so that there are just three columns: name, track, position. It then extracts names and counts separately for each "Finish" track using `query()`. I think what you are now saying that you want to match track names using some complex logic where `Detroit 500` should be considered the same as `Detroit Grand Prix`. Please do not present new requirements after you have received an answer. Ask only [one question per post](https://meta.stackexchange.com/a/222741). Post a [new question](https://webapps.stackexchange.com/questions/ask) instead. – doubleunary Jul 16 '23 at 08:37