Google Sheets - smart filtering

Question

So I need to do something which might be a standard feature in spreadsheets, but I have no idea how.

I have a landing page that processes T-shirt purchases by families. Each row represents an order, which can be for one or multiple children at once:

Now what really interests me in order to get the shirts ready is a row for each kid separately, and their shirt size. That way I can filter by shirt size and know that there are a total of 3 Small, 2 Medium, 3 Large (and each row will contain the full name for that shirt).

So really I would like somehow to process the above table into the following table (this can be in a new worksheet, or some other place):

I was going to start writing an Apps Script for this, but before I did I was wondering if there's anything simpler than writing code.

Answer 1

=LET(raw,A:H,_a,INDEX(raw,,1),_r,COUNTA(_a),
  data,TRANSPOSE(SORT(SORTN(FILTER(raw,_a<>""),_r-1,,SEQUENCE(_r),0),1,1)),
  seq,SEQUENCE(COLUMNS(raw)),all,TRANSPOSE(ARRAY_CONSTRAIN(data,2,_r-1)),
  label,LAMBDA(r, LAMBDA(c,a, IFERROR(LET(child,{all,
     ARRAYFORMULA(TEXT(SEQUENCE(ROWS(a)),"")&"Child "&c),a},
        FILTER(child,INDEX(child,,4)<>"")),{"","","","",""}))(r,
           TRANSPOSE(FILTER(data,(seq=2*r+1)+(seq=2*r+2))))),
  out,{"Invoice No.","Invoice Total","Child #","Name","Size";
     SORT({label(1);label(2);label(3)})},
  FILTER(out,INDEX(out,,1)<>""))

Answer 2

=query({query(A2:H,"Select A,B,'Child 1',C,D");
        query(A2:H,"Select A,B,'Child 2',E,F");
        query(A2:H,"Select A,B,'Child 3',G,H")},"Where Col4<>'' order by Col1",0)

Answer 3

Many people prefer "one-liner" versions of formulas that look like line noise from a modem in days of yore. Those formulas are none the "more efficient", but if you want an unreadable version, you can use this:

=let(d,Sheet1!A1:H,ncr,2,ncg,2,rs,row(d),cs,column(d),nr,max(index((row(d)-rs+1)*iferror(d<>""))),t,offset(d,0,0,nr),h,{offset(t,0,0,1,ncr),"Category",offset(t,0,ncr,1,ncg)},reduce(h,offset(t,1,ncr,nr-1,columns(t)-ncr),lambda(r,c,if((c="")+mod(column(c)-cs-ncr,ncg),r,{r;{offset(t,row(c)-rs,0,1,ncr),offset(t,0,column(c)-cs,1,1),offset(c,0,0,1,ncg)}}))))

More generally, there are many ways to unpivot data where groups of similar columns repeat across rows. The formula below should be easy to apply to many kinds of use cases. It lets you define the location of data, the number of left-hand side columns to put on every result row, and the number of columns per group to unpivot together. The formula figures out the rest. It does not use split() to avoid side effects such as coercing the text string 1 2 3 to the date 2 January 2023. It also does not use query() that will return null when a value does not match the type of the majority of values in its column.

To unpivot the data shown in the question, choose Insert > Sheet and put the formula in cell A1 of the new sheet:

=let( 

  data, Sheet1!A1:H, 
  numColsToRepeat, 2, 
  numColsPerGroup, 2, 

  rowStart, row(data), 
  colStart, column(data), 
  numRows, max(index((row(data) - rowStart + 1) * iferror(data <> ""))), 
  table, offset(data, 0, 0, numRows), 
  headers, { 
    offset(table, 0, 0, 1, numColsToRepeat), 
    "Category", 
    offset(table, 0, numColsToRepeat, 1, numColsPerGroup) 
  }, 
  unpivot, reduce( 
    headers, offset(table, 1, numColsToRepeat, numRows - 1, columns(table) - numColsToRepeat), 
    lambda( 
      result, cell, 
      if( 
        (cell = "") + mod(column(cell) - colStart - numColsToRepeat, numColsPerGroup), 
        result, 
        { 
          result; 
          { 
            offset(table, row(cell) - rowStart, 0, 1, numColsToRepeat), 
            offset(table, 0, column(cell) - colStart, 1, 1), 
            offset(cell, 0, 0, 1, numColsPerGroup) 
          } 
        } 
      ) 
    ) 
  ), 
  unpivot 
)

The formula will weed out records where a value is missing in the full name column. If you want to keep those records, delete the (cell = "") + bit.

To further process the results, add a query() wrapper to the unpivot in the last line.

The results look like this:

Receipt no.	Amount	Category	Child	Shirt size
123456	$200.00	Child 1	John Smith	S
123456	$200.00	Child 2	Mary	L
123456	$200.00	Child 3	Peter	S
4545743	$20.00	Child 1	Lilly	M
453333	$50.00	Child 1	Tracy	L
453333	$50.00	Child 2	Bill	M
453333	$50.00	Child 3	Jill	M
3212211231	$75.00	Child 1	Michael	S
78764343	$55.00	Child 1	Terry	L