Named function🧮 for Google Sheets. 🦒Unpivot

Sample Spreadsheet: copy, learn, use.

Why use Univot? For reports and pivot tables.

Usage

Use as a named function:

=UNPIVOT(rowData, columnData, firstDataCell)

rowData—data to repeat from rows
columnData—data to repeat from columns ~ headers,
firstDataCell — first cell containing data to unpivot.

The result always has intersecting values in column 1. Benefit from using a filter like this:

=let(unp, UNPIVOT(B5:E14,F3:I4,F5), FILTER(unp,INDEX(unp,,1)<>””))

↑ The filter shows only rows with not-empty results.

Other Pro’s:

• Can operate images
• Fills blank cells from the previous ones. The result has no blanks.

Definition

This is one of the possible definitions.

=LET(hrow,B5:E14, hcol,F3:I4, dtcell,F5, c,"ힽ",
 hrow_filled,FILL_DOWN(hrow), 
 hcol_filled,FILL_DOWN(TRANSPOSE(hcol)), 
 rws,ROWS(hrow_filled), rowseq,SEQUENCE(rws),
 cls,ROWS(hcol_filled), colseq,SEQUENCE(1,cls),
 dt,OFFSET(dtcell,,,rws,cls),
 unpivbase,INDEX(SPLIT(FLATTEN(rowseq&c&colseq&c&dt),c)), 
   rwindx,index(unpivbase,,1), 
   clindx,index(unpivbase,,2), 
   vals,index(unpivbase,,3),
HSTACK(
  vals, 
  GENERATE_ARRAY(hrow_filled,rwindx), 
  GENERATE_ARRAY(hcol_filled,clindx)))

Interesting happens here in “unpivbase”. We’ll achieve the same result without the “SPLIT-JOIN” 🎅🏼 hack. Here’s the better version:

=LET(hrow,B5:E14, hcol,F3:I4, dtcell,F5,
 hrow_filled,FILL_DOWN(hrow), 
 hcol_filled,FILL_DOWN(TRANSPOSE(hcol)), 
 rws,ROWS(hrow_filled),
 cls,ROWS(hcol_filled), 
 rwindx,INDEX(ROUNDUP(SEQUENCE(rws*cls)/cls)),
 clindx,INDEX(MOD(SEQUENCE(rws*cls)-1,cls)+1),
 dt,OFFSET(dtcell,,,rws,cls),
 dt_flat,map(rwindx,clindx, LAMBDA(r,c,index(dt,r,c))),
HSTACK(
 dt_flat, 
 GENERATE_ARRAY(hrow_filled,rwindx), 
 GENERATE_ARRAY(hcol_filled,clindx)))

↑ The second definition works a bit faster and does not have side effects.

Dependent definitions:

FILL_DOWN—fills empty values from the last filled cell down:

=GENERANTE_CELLROWS(data,
TRANSPOSE(SCAN(0,TRANSPOSE(
MAKEARRAY(ROWS(data),COLUMNS(data),LAMBDA(r,c,IF(INDEX(data,r,c)="",0,r)))
),LAMBDA(ini,v,IF(v=0,ini,v)))))

GENERATE_ARRAY —generates an array of given row indexes:

=lambda(sr,MAKEARRAY(rows(indexes),COLUMNS(data),
LAMBDA(r,c,INDEX(data,index(sr,r),c))))(indexes)