Named function🧮 for Google Sheets. 🦒Unpivot

Max Makhrov
1 min readAug 9

--

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)

--

--

Max Makhrov

Google Sheets Developer, master of Online Accounting

Recommended from Medium

Lists

See more recommendations