New Excel Functions

Silver Contributor

May 04, 2022

Returning to our EXPAND discussion (or should it be EXTEND), I was trying to refactor a Thunk calculation yesterday and found I needed to embed the new EXPAND within my Expandλ function. Basically I had taken unique terms from a random array row by row

arrayϑ  = BYROW(array, Uniqueλ);
Uniqueλ = LAMBDA(x,Thunkλ(SORT(UNIQUE(x,1),,,1)));

The worksheet formula to expand the column of thunks looks simple enough

= Expandλ(arrayϑ)

where the Lambda function is defined by / hides

Expandλ = LAMBDA(ϑ,
    LET(
        n₁, ROWS(ϑ),
        n₂, MAX(MAP(ϑ,Columnsλ)),
        MAKEARRAY(n₁,n₂,
            LAMBDA(i₁,i₂,
                INDEX(EXPAND(INDEX(ϑ,i₁,1)(),1,n₂,""),i₂)
            )
        )
    )
);

The new EXPAND function is used to extend the rows to match the longest row rather than 'filling' with #REF! errors. I have no idea whether playing with Thunks is a temporary requirement but, if it is required longer-term, I think this function to unlock the information hidden within the thunk array should make life simpler. The worksheet function even looks acceptable!

Blog Post

Announcing New Text and Array Functions