How could I convert a wide table to long format using a formula in Google Sheets? Within each group, the values should be alphabetically sorted (e.g. anna is first in the expected output). Blank cells should be ignored.
The expected output is the data summarised in long format:
You can try this formula. Change the range in the Let function to accommodate your dataset (here listed as A:D based on the question).
=let(range,A:D,toprow,row(index(range,1,1)),data,arrayformula(split(transpose(split(textjoin(",",true,tocol(BYCOL(range,LAMBDA(bc,vstack("|"&filter(bc,row(bc)=toprow),sort(filter(bc,row(bc)>toprow),1,1)))),1,1)),"|")),",")),wraprows(torow(BYROW(data,LAMBDA(k,makearray(1,(COUNTA(k)-1)*2,lambda(r,c,if(isodd(c),index(k,r,1),(index(k,r,(c/2)+1))))))),1),2))