I have an input like below in Google Sheet:

I need to generate rows and columns with the above available data given below:

Conditions and tried formula:

1. WORKED - To reverse and transpose column 'Items' from input to generated column 'Item Order' in output. Below formula is used for that and worked.

   =
   Filter(
   {
   TRIM(FILTER(TRANSPOSE(C2:F2),TRANSPOSE(C2:F2)<>""));
   QUERY(SORT(TRANSPOSE(C3:F3),SEQUENCE(COLUMNS(C3:F3)),),"where Col1 is not null",0)
   },
   {
   FILTER(TRANSPOSE(C2:F2),TRANSPOSE(C2:F2)<>"");
   QUERY(SORT(TRANSPOSE(C3:F3),SEQUENCE(COLUMNS(C3:F3)),),"where Col1 is not null",0)
   }<>"
    ")
   

2. I need to generate column 'Side' and 'Row' in output for respective items from input. Here, the last item in front side needs to be assigned 'Right End' and the first item in back side needs to be assigned 'Left End'. I'm not sure how to include this logic for generating these columns to above formula.

CodePudding user response：

Try

={arrayformula(split({flatten(OFFSET(C2,,,,COUNTA(C2:G2)-1)&"|"&B2&"|"&A2);OFFSET(C2,,COUNTA(C2:G2)-1)&"|right end|"&A2},"|"));
query(sort(arrayformula(split({column(C3)&"|"&C3&"|left end|"&A3;
flatten(column(OFFSET(C3,,1,,COUNTA(C3:G3)-1))&"|"&OFFSET(C3,,1,,COUNTA(C3:G3)-1)&"|"&B3&"|"&A3)},"|")),1,false),"select Col2,Col3,Col4")}