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.
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.
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
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?