3

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:

Worksheet

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):

Desired table

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.

1
  • The term you're looking for is called Unpivot, it's very simple but should be doable with some array formulas May 4, 2023 at 18:52

3 Answers 3

2
=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)<>""))

3
  • 1
    Thanks this did the trick, will now go through it slowly to understand why :)
    – Cauthon
    May 5, 2023 at 6:58
  • 1
    Noticed that the formula will error out if you delete one or more names in the first data row. Could not immediately see how to fix that. May 5, 2023 at 14:25
  • Thanks @doubleunary! The first-row issue was due to a mistake I made calculating seq : I should have used ROWS instead of COUNTA. Further testing though showed me that the formula would error out if there were no orders that included a "Child 2" or "Child 3." That may not be an issue if there is at least one order for each, however, I implemented a fix to plug it anyway.
    – Blindspots
    May 5, 2023 at 15:37
3
=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)

enter image description here

1
  • Boom! Mic drop. So efficient. I'm embarrassed.
    – Blindspots
    May 5, 2023 at 16:57
1

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
4
  • When I try this one, I'm getting an error Error: Calculation limit was reached while trying to compute this formula. May 4, 2023 at 19:54
  • Chances are that you have many blank rows at the bottom of the sheet. Delete unneeded blank rows. Consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. May 5, 2023 at 6:33
  • (Thanks for this Blank Sheet Maker link!) tried in a blank sheet and there is no error, but based on the OP sample data it will generate two rows per order with name and size on different rows. See docs.google.com/spreadsheets/d/… May 5, 2023 at 13:38
  • 1
    Edited the answer. The formula now lets you define the number number of columns to unpivot together. Also added the count of rows to guard against very large mostly blank spreadsheets. See the sample spreadsheet. May 5, 2023 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.