UNIQUE of Rectangular Range Using MAKEARRAY in Excel – 2416

Posted on by Bb

Another new LAMBDA helper function in Excel is MAKEARRAY. This video shows a simple example, and then tackles the problem from episode 2317 of how to unwind a rectangular range into a single column so you can pass it to UNIQUE and SORT.
The formula used in the video is:
=LET(array,B2:G15,
rowcount,ROWS(array),
colcount,COLUMNS(array),
itemcount,rowcount*colcount,
MAKEARRAY(itemcount,1,
LAMBDA(r,c,
INDEX(array,
LET(a,MOD(r,rowcount),IF(a=0,rowcount,a)),
ROUNDUP(r/rowcount,0))
)))

MrExcel.comTue, August 10, 2021 12:09am