Just picking up on the suggested solution: Maybe the HSTACK part:
=HSTACK(DROP(REDUCE("",TOCOL(IF(COLUMN(C1:G1),A2:A6&"-"&B2:B6)),LAMBDA(a,i,VSTACK(a,TEXTSPLIT(i,"-")))),1),TOCOL(IF(ROW(A2:A6),C1:G1)),TOCOL(IF(ROW(A2:A6),C2:G6)))
is unpivoted a little bit nicer in the following way:
=LET(a,A2:A6,b,C1:G1,c,B2:B6,d,C2:G6,HSTACK(TOCOL(IFNA(a,b)),TOCOL(IFNA(c,b)),TOCOL(IFNA(b,a)),TOCOL(d)))
If you're opted in the Beta channel and have already access to the relatively new group function, then the same result can be achieved relatively easily via:
=LET(a,A2:A6,b,C1:G1,c,B2:B6,d,C2:G6,x,HSTACK(TOCOL(IFNA(a,b)),TOCOL(IFNA(b,a)),TOCOL(IFNA(c,b)),TOCOL(d)),GROUPBY(TAKE(x,,2),TAKE(x,,-1),SUM,,0,,TAKE(x,,-1)>CHOOSECOLS(x,3)))
Bookmarks