1

I have a google form where team coaches input which players played on a given day.

They also input if they were Man of the Match, Yellow/Red Cards Received, and how many Goals they scored.

Google Form Tick Box Grid

When the form is submitted there are blank Columns where players did not play. There are 11 age groups and 4 teams within each age group, roughly 400 players, therefore 400 columns!

This is team WHITES.

enter image description here

In Columns to the right (after the last player of the last team) i need to register only the players that played and omit the non playing players.

I have the following code but it's not extracting the correct data.

=ARRAYFORMULA(
IF(ISBLANK($A1:$A),"",
IF(ROW($A1:$A)=ROW($A$1),"Player "&COLUMN(A1),


QUERY(TRANSPOSE(M2:JM),"Select Col1 where Col1 is not null limit 1",0))))

As it is automating a Google Form the first three lines of the formula can be ignored. Its from QUERY( which is the problem.

The formula in JN1 is then copied right for 16 Players

enter image description here

Dave should be Player 3 with 1 Goal and FRED should be Player 4 with 2 Goals and the rest should be blank.

Is Query/Transpose the correct way extract this data or something else?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.