User Profile
Patrick2788
Silver Contributor
Joined Oct 18, 2019
User Widgets
Recent Discussions
Re: WrapRows2Dλ / WrapCols2Dλ: Fast, efficient 2D wrapping without flattening
From the start my approach was to avoid flattening the input array. I have several other functions in my library that use pure i/j indexing and the times are always fast (Especially when thunked. Excel will not evaluate 'i' or 'j' twice but only when used within INDEX(array,i(),j() for example ). I try to avoid Lambda helpers for re-shaping.WrapRows2Dλ / WrapCols2Dλ: Fast, efficient 2D wrapping without flattening
Background One of Excel's biggest weaknesses is in working with 2D arrays as objects that can be re-shaped. WRAPROWS/WRAPCOLS do not accept 2D arrays (#VALUE!) and are strictly for shaping 1D arrays. The usual workarounds involve flattening with TOROW/TOCOL then re-shaping with WRAPROWS/WRAPCOLS, REDUCE used as an iterator to stack (do-able but slow), and even MAKEARRAY (do-able, but not instinctive and slow). The Goal Fast, efficient 2D wrapping without flattening. The Approach Pure deferred i/j indexing with modular math and sequencing. The function and sample workbook is provided below. I welcome any and all feedback: suggestions for improvement, your approach to 2D shaping, etc. // Fast, efficient 2D wrapping without flattening //----------------------------------------------------------------------------------- //---WrapCols2Dλ--- //----------------------------------------------------------------------------------- //Author: Patrick H. //Date: 1/28/2026 //Version: 1.0 // //Description: //Wrap a 2D array into column blocks of a specified width while preserving row height. //The wrapped blocks are stacked vertically in the output. //Jagged or uneven final blocks are padded with NA() by default, unless a fill value //is supplied via the optional pad_with parameter. // //----------------------------------------------------------------------------------- //Parameter Description //array - 2D array to be wrapped (1D arrays not supported) //new_width - Number of columns in each wrapped block // //Optional Description //pad_with - Fill value used to pad incomplete blocks. If omitted, NA() is used. // //Lambda called: Echoλ WrapCols2Dλ= LAMBDA( array, new_width, [pad_with], //Check inputs LET( //Shape h, ROWS(array), w, COLUMNS(array), blocks, CEILING(w/new_width,1), //Optional pad_with, IF(ISOMITTED(pad_with),NA(),pad_with), //Total rows when wrapped r, blocks * h, //Scenarios Is1D?, OR(h = 1,w = 1), IsScalar?, AND(h = 1, w = 1), InvalidDim?,new_width >= w, SpillRisk?, r > 1048576, //Logic gate IF(IsScalar?,#VALUE!, IF(Is1D?,#VALUE!, IF(InvalidDim?,"#WIDTH!", IF(SpillRisk?,#NUM!, //Proceed LET( //Indices - deferred modulo, LAMBDA(MOD(SEQUENCE(r),h)), i, LAMBDA(IF(modulo() = 0, h, modulo()) * SEQUENCE(,new_width,1,0)), j, LAMBDA(Echoλ(SEQUENCE(r / h,,1,new_width),h) + SEQUENCE(,new_width,0,1)), //Wrapped array result, IFERROR(INDEX(array,i(),j()),pad_with), result ))))))); //----------------------------------------------------------------------------------- //---WrapRows2Dλ--- //----------------------------------------------------------------------------------- //Author: Patrick H. //Date: 1/28/2026 //Version: 1.0 // //Description: //Wrap a 2D array into row blocks of a specified height while preserving column width. //The wrapped blocks are stacked horizontally in the output. //Jagged or uneven final blocks are padded with NA() by default, unless a fill value //is supplied via the optional pad_with parameter. // //----------------------------------------------------------------------------------- //Parameter Description //array - 2D array to be wrapped (1D arrays not supported) //new_height - Number of rows in each wrapped block // //Optional Description //pad_with - Fill value used to pad incomplete blocks. If omitted, NA() is used. // //Lambda called: Echoλ WrapRows2Dλ= LAMBDA( array, new_height, [pad_with], //Check inputs LET( //Shape h, ROWS(array), w, COLUMNS(array), blocks, CEILING(h/new_height,1), //Optional pad_with, IF(ISOMITTED(pad_with),NA(),pad_with), //Total columns when unwrapped c, blocks * w, //Scenarios Is1D?, OR(h = 1,w = 1), IsScalar?, AND(h = 1, w = 1), InvalidDim?,new_height >= h, SpillRisk?, c > 16384, //Logic gate IF(IsScalar?,#VALUE!, IF(Is1D?,#VALUE!, IF(InvalidDim?,"#HEIGHT!", IF(SpillRisk?,#NUM!, //Proceed LET( //Indices - deferred i, LAMBDA(TOROW(Echoλ(SEQUENCE(,blocks,1,new_height),w)) + SEQUENCE(new_height,,0,1)), modulo, LAMBDA(MOD(SEQUENCE(,w * blocks),w)), j, LAMBDA(IF(modulo()=0,w,modulo()) * SEQUENCE(new_height,,1,0)), //Wrapped array result, IFERROR(INDEX(array,i(),j()),pad_with), result ))))))); //----------------------------------------------------------------------------------- //Echoλ //----------------------------------------------------------------------------------- //Author: Patrick H. //Date: 11/7/2025 //Version: 1.0 //Description: //Repeat each element in a supplied 1D array by specifying the repeat counts. //Arrays and scalars are supported. //----------------------------------------------------------------------------------- //vector - 1D array or scalar to be echoed //repeat - 1D array of repeat counts (must be numeric and ≥1) Echoλ = LAMBDA( vector, repeat, //Check inputs IF(OR(ISTEXT(repeat),repeat<=0),#VALUE!, LET( //Flatten inputs vector, TOCOL(vector), repeat, TOCOL(repeat), //Dimensions and row indexing V↕, ROWS(vector), R↕,ROWS(repeat), r, IF(V↕<>R↕,EXPAND(repeat,V↕,,@TAKE(repeat,-1)), repeat), i, SEQUENCE(ROWS(r)), m, MAX(r), idx, LAMBDA(TOCOL(IF(SIGN(r-SEQUENCE(,m,0,))=1,i,NA()),2)), //Unwrap idx but defer delivery until function invocation deliver, LAMBDA(INDEX(vector,idx())), deliver ))()); Workbook attached and linked in case this forum gobbles it up! Patrick2788/Excel-Lambda: Excel Lambda modules Excel-Lambda/Wrap2D Demo.xlsx at main · Patrick2788/Excel-Lambda Excel Lambda modules. Contribute to Patrick2788/Excel-Lambda development by creating an account on GitHub. github.comRe: How does Excel calculate named formulas and materialized named ranges?
Excel favors eager evaluation in most situations. In your two examples, the timings were about the same. The example using trim range was the quickest. ManualAllocRng = OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5); // Average of 5 timings // Elements 1.25 million // TestAλ 0.21 // TestBλ 0.21 // TestCλ 0.18 TestAλ = LAMBDA( x, LET( rng, ManualAllocRng, a, DROP(rng,,2), b, TAKE(rng,,-2), result, HSTACK(a,b)+x, result )); TestBλ = LAMBDA( x, LET( a, DROP(ManualAllocRng,,2), b, TAKE(ManualAllocRng,,-2), result, HSTACK(a,b)+x, result )); TestCλ= LAMBDA( x, LET( rng, Sheet1!$A:.$E, a, DROP(rng,,2), b, TAKE(rng,,-2), result, HSTACK(a,b)+x, result)) The way in which the first two examples are authored may have an effect on the calculation speed, but it really depends on what you're doing with the arrays. Excel seems to prioritize allocating memory to spill re-sizing but it's not easy to re-produce the situation. I've seen functions take 12 seconds to calculate even with the function terminating within an IF even before the LET block comes in. There are ways to stop the eager evaluation and even speed up functions with some well-placed thunks. In the examples A/B above, there were no differences in the calc speed.Re: Another issue with Value transformation.
For Excel 365, this will strip out just about anything (even stuff CLEAN can't handle). =--REGEXREPLACE(B6,"[^A-Za-z0-9 .]|[\x{00A0}\x{200B}-\x{200D}]|[\t\n\r]|[\x{1F600}-\x{1F64F}]","") This was part of a diagnostic lambda I had created called Revealλ which provided a cell-for-cell description of what Excel saw in each cell (e.g. Number, number stored as text, special character, etc.).Re: Advanced Formula Editor: converting functions Names into a module
I don't believe there's an elegant solution at the moment. You could scoop each function out of the name manager, place them in a cell (it will evaluate to a #CALC! error), and then import from grid from the Modules tab of AFE. I used to build functions through Names | Functions but the space is very limited. Everything begins and ends in the workbook module!Re: The Diagonal Suite: Gentle thunking goes a long way!
It looks like there's a bit more room for some gentle thunking to reduce calculation times even more. For example, with Traverseλ 'result' is deferred until the very end. I snuck the () in at the end so it's not needed at the sheet level. // --- Workbook module --- // A file of name definitions of the form: // name = definition; //Author: Patrick H. //Date: 10/27/2025 //Version: 1.0 //Repo: Please see The Diagonal Suite for a full suite of functions. //------------------------------------------------------------------------------------------- //Traverseλ - Directional Axis Remapper //------------------------------------------------------------------------------------------- //The selected axis is remapped to the top-left traversal order. //Accepted directions: // "NE" or 1 → Northeast (↗) // "SE" or 2 → Southeast (↘) // "SW" or 3 → Southwest (↙) //Parameters: //array → 2D input array (scalars not accepted) //new_axis → Axis direction ("NE", "SE", "SW" or 1–3) Traverseλ = LAMBDA( array, new_axis, //Input validation IF(OR(ROWS(array)=1,COLUMNS(array)=1), "#2D-ARRAY!", IF(AND(ISNUMBER(new_axis),OR(new_axis<=0,new_axis>3)),"#AXIS!", LET( //Dimensions i, ROWS(array), j, COLUMNS(array), //Axis traversal indices (deferred) x_NE, LAMBDA(SEQUENCE(j,,1,0)*SEQUENCE(,i)), y_NE, LAMBDA(SEQUENCE(j,,j,-1)*SEQUENCE(,i,1,0)), x_SE, LAMBDA(SEQUENCE(i,,i,-1)*SEQUENCE(,j,1,0)), y_SE, LAMBDA(SEQUENCE(i,,j,0)+SEQUENCE(,j,0,-1)), x_SW, LAMBDA(SEQUENCE(j,,i,0)+SEQUENCE(,i,0,-1)), y_SW, LAMBDA(SEQUENCE(j,,1)*SEQUENCE(,i,1,0)), //Axis mode selection mode, IF(ISNUMBER(new_axis),new_axis, SWITCH(new_axis,"NE",1,"SE",2,"SW",3,1)), //Index selection x, CHOOSE(mode,x_NE,x_SE,x_SW), y, CHOOSE(mode,y_NE,y_SE,y_SW), //Unwrap indices but defer results until function invocation result, LAMBDA(INDEX(array,x(),y())), result ) ))()); This is fascinating in how a simple LAMBDA() tells Excel's formula parser to not read/fully evaluate a line and to treat it as "unrealized code". Timings: Array New axis Calc Time Calc Time - 1 more thunk 100000x100 SE 1.98 1.7Re: The Diagonal Suite: Gentle thunking goes a long way!
Thank you for taking a look! I've been taking a lot of my unfinished projects and rolling them into polished generalized functions lately. At one time I was determined to find an elegant way to unwrap a row of thunks, but I think you have that covered! (I'll need to revisit that module soon. My methods tend to change over time as I write more functions and learn new things.) The eye opener was messing with Spiralλ in thunking all sequencing and arithmetic wherever possible and watching the calc time go down. The analogy I like to think of with using thunks in this manner is buying food in bulk at the grocery store. It's not mentioned at checkout usually, but the cost of the bag's weight (tare weight) is subtracted from the cost of food when determining the price. The cost of using LET ("the bag") and not heavily-nested direct evaluation is slightly slower calc times, but we never notice it with small data sets. The gentle thunks give us back the tare weight.Re: The Diagonal Suite: Gentle thunking goes a long way!
I had considered including Traverseλ in the signature of ByDiagλ but pulled back because I thought anti-diagonal aggregations would not be common (and it would be one too many parameters). There seems to be a dearth of good diagonally arranged examples online (Unless I'm not searching with the right terms). I'll have a look at accounts receivable and depreciation schedules. Thank you!The Diagonal Suite: Gentle thunking goes a long way!
I've become a big advocate for gentle thunking - using thunks to delay eager evaluation wherever possible in generalized Lambda development. The timings are quicker and the logic is cleaner. On the other hand, thunking the results of MAP, BYROW, or BYCOL - especially when it leads to rows of thunks - tends to introduce recombination overhead and complexity. I think thunking is often dismissed as “too complex,” and that’s understandable if someone’s first exposure involves unwrapping a row of thunks. When used gently thunking becomes indispensable. Typically, I introduce the thunks after the initial benchmarking to see the difference in the calculation times and the after is always quicker. To illustrate, I’ll share The Diagonal Suite - a collection of functions where thunking is used at every opportunity. Simple, clean, deferred logic. What are your thoughts on gentle thunking? Where have you found it helpful/harmful in your own Lambda development? //The Diagonal Suite - Version 1.0 - 10/27/2025 //Author: Patrick H. //Description: Directional traversal and diagonal logic for 2D arrays. // Functions: // • Traverseλ - Directional traversal engine // • ByDiagλ - Diagonal-based aggregation // • DiagMapλ - Wrapper for diagonal matrix extraction // • DiagIndexλ - Targeted diagonal extraction // • Staircaseλ - Construct diagonal staircases from a vector or 2D array //──────────────────────────────────────────────────────────── //------------------------------------------------------------------------------------------- //Traverseλ - Directional Axis Remapper //------------------------------------------------------------------------------------------- //The selected axis is remapped to the top-left traversal order. //Accepted directions: // "NE" or 1 → Northeast (↗) // "SE" or 2 → Southeast (↘) // "SW" or 3 → Southwest (↙) //Parameters: //array → 2D input array (scalars not accepted) //new_axis → Axis direction ("NE", "SE", "SW" or 1–3) Traverseλ = LAMBDA( array, new_axis, //Input validation IF(OR(ROWS(array)=1,COLUMNS(array)=1), "#2D-ARRAY!", IF(AND(ISNUMBER(new_axis),OR(new_axis<=0,new_axis>3)),"#AXIS!", LET( //Dimensions i, ROWS(array), j, COLUMNS(array), //Axis traversal indices (deferred) x_NE, LAMBDA(SEQUENCE(j,,1,0)*SEQUENCE(,i)), y_NE, LAMBDA(SEQUENCE(j,,j,-1)*SEQUENCE(,i,1,0)), x_SE, LAMBDA(SEQUENCE(i,,i,-1)*SEQUENCE(,j,1,0)), y_SE, LAMBDA(SEQUENCE(i,,j,0)+SEQUENCE(,j,0,-1)), x_SW, LAMBDA(SEQUENCE(j,,i,0)+SEQUENCE(,i,0,-1)), y_SW, LAMBDA(SEQUENCE(j,,1)*SEQUENCE(,i,1,0)), //Axis mode selection mode, IF(ISNUMBER(new_axis),new_axis, SWITCH(new_axis,"NE",1,"SE",2,"SW",3,1)), //Index selection x, CHOOSE(mode,x_NE,x_SE,x_SW), y, CHOOSE(mode,y_NE,y_SE,y_SW), //Unwrap indices and get results result, INDEX(array,x(),y()), result ) ))); //------------------------------------------------------------------------------------------- //ByDiagλ - Diagonal-based aggregation //------------------------------------------------------------------------------------------- //Apply an ETA function or Lambda to diagonals //Parameters: //array → 2D input array (scalars not accepted) //[function] → ETA function or Lambda applied to diagonals //[row_wise_stack?] → Optional: Display results as a vertical stack ByDiagλ = LAMBDA( array, [function], [row_wise_stack?], //Check array input ValidateDiagλ(array,,function,row_wise_stack?, LET( //Optional parameters No_Function, ISOMITTED(function), No_row_wise_stack,ISOMITTED(row_wise_stack?), //Dimensions i, ROWS(array), j, COLUMNS(array), //Diagonal count k, MIN(i,j), //Indices - deferred r, LAMBDA(SEQUENCE(k)*SEQUENCE(,j,1,0)), y, LAMBDA(SEQUENCE(k)+SEQUENCE(,j,0,1)), c, LAMBDA(IF(y()>j,NA(),y())), //Unwrap indices, shape, and aggregate result, IFNA(INDEX(array,r(),c()),""), shaped, IF(No_row_wise_stack,result,TRANSPOSE(result)), final, IF(No_Function,shaped, IF(No_row_wise_stack,BYCOL(shaped,function), BYROW(shaped,function))), final ))); //------------------------------------------------------------------------------------------- //DiagMapλ - Wrapper (Calls ByDiagλ) to extract diagonals as 2D matrix //------------------------------------------------------------------------------------------- //Calls ByDiagλ to extract the diagonals from a 2D array. //Parameters: *Please see ByDiagλ for descriptions.** DiagMapλ = LAMBDA( array, [row_wise_stack?], ByDiagλ(array,,row_wise_stack?) ); //------------------------------------------------------------------------------------------- //DiagIndexλ - Targeted diagonal extraction //------------------------------------------------------------------------------------------- //Extract a diagonal or anti-diagonal vector from a 2D array. //Parameters: //array → 2D input array (scalars not accepted) //col_index → Column number to start from. Negative = anti-diagonal DiagIndexλ = LAMBDA( array, col_index, //Input checks ValidateDiagλ(array,col_index,,, LET( //Dimensions i, ROWS(array), j, COLUMNS(array), //Diagonal direction: +1 = SE, –1 = SW s, SIGN(col_index), //Determine diagonal length based on bounds k, IF(s>0, MIN(i, j + 1 - col_index), MIN(i, ABS(col_index))), start, IF(s<0,ABS(col_index),col_index), //Indices - deferred x, LAMBDA(SEQUENCE(k)), y, LAMBDA(SEQUENCE(k,,start,s)), //Unwrap indices and extract vector deliver, INDEX(array,x(),y()), deliver ))); //------------------------------------------------------------------------------------------- //Staircaseλ — Construct diagonal staircases from a vector or 2D array //------------------------------------------------------------------------------------------- //Parameters: //array → Input array (flattened to vector row-wise) //block_size → Number of rows/columns per staircase block //[block_offset] → Optional padding between staircases //[IsHorizontal?] → Optional toggle for column-wise orientation //[IsAntiDiag?] → Optional toggle to display staircase anti-diagonal. Staircaseλ = LAMBDA( array, block_size, [block_offset], [IsHorizontal?], [IsAntiDiag?], //Check inputs ValidateStaircaseλ(array,block_size,block_offset, LET( //Check optional parameters no_Block_Offset, ISOMITTED(block_offset), zero_Offset, block_offset=0, col_offset, IF(No_Block_Offset,0,block_offset), IsVertical?, ISOMITTED(IsHorizontal?), Not_Anti_Diag, ISOMITTED(IsAntiDiag?), //Convert to vector and get dimensions flat, TOCOL(array), k, COUNTA(flat), seq, LAMBDA(SEQUENCE(k)), V, TOROW(EXPAND(WRAPROWS(seq(),block_size),, block_size+block_offset,0)), width, COLUMNS(V), //Anchors and indices - deferred i, LAMBDA(SEQUENCE(block_size)*SEQUENCE(,width,1,0)), col_arr, LAMBDA(IF(Not_Anti_Diag,SEQUENCE(,width), SEQUENCE(,width,width,-1))), j, LAMBDA(MOD(col_arr(),block_size+block_offset)), j_, LAMBDA(IF((no_Block_Offset)+(zero_Offset), IF(j()=0,block_size,j()),j())), idx, LAMBDA(IF(i()=j_(),V,NA())), //Obtain results, shape, and calculate result, DROP(IFNA(INDEX(flat,idx()),""),,-col_offset), final, IF(IsVertical?,TRANSPOSE(result),result), final ))); //---------------------Error Handling & Validation--------------------------- //Validates inputs for Staircaseλ. Please see Staircaseλ for parameter //descriptions. ValidateStaircaseλ = LAMBDA( array, block_size, [block_offset], [on_valid], LET( //Checks NotArray,TYPE(array)<>64, Invalid_block_size, OR(ISTEXT(block_size),block_size<=0,block_size>COUNTA(array)), Invalid_block_offset, OR(ISTEXT(block_offset),block_offset<0), //Logic gate IF(NotArray, "#NOT-ARRAY!", IF(Invalid_block_size, "#BLOCK-SIZE!", IF(Invalid_block_offset,"#BLOCK-OFFSET", on_valid)))) ); //---------------------Error Handling & Validation--------------------------- //Validate inputs for ByDiagλ, DiagMapλ, and DiagIndexλ. //*Please see those functions for parameter descriptions.* ValidateDiagλ= LAMBDA( array, [col_index], [function], [row_wise_stack?], [on_valid], LET( //---Checks--- //Array input IsArray?, TYPE(array)=64, Not_Array, NOT(IsArray?), //Col_index No_Col_Index, ISOMITTED(col_index), Col_Index_Included, NOT(No_Col_Index), Not_Valid_Col_Index?, NOT(AND(col_index<>0, ABS(col_index)<=COLUMNS(array))), //Function No_Function, ISOMITTED(function), Function_Included, NOT(No_Function), Invalid_Function?, AND(ISERROR(BYROW({1,1},function))), //Shaping input RowWiseStack?, NOT(ISOMITTED(row_wise_stack?)), //Deterine which function is being validated DiagIndex, Col_Index_Included, ByDiag, AND(No_Col_Index, Function_Included), DiagMap, AND(No_Col_Index, No_Function), //Logic gates //DiagIndexλ a, IF(Not_Array, "#NOT-ARRAY!", IF(Not_Valid_Col_Index?,"#COLUMN-INDEX!", on_valid)), //ByDiagλ b, IF(Not_Array, "#NOT-ARRAY!", IF(Invalid_Function?, "#FUNCTION!", on_valid)), //DiagMapλ c, IF(Not_Array, "#NOT-ARRAY!", on_valid), //Logic gate selection decide, IF(DiagIndex,a, IF(DiagMap,c, IF(ByDiag,b, "#UNROUTED!"))), decide )); //End of The Diagonal Suite - Version 1.0 //Author: Patrick H.
