User Profile
Patrick2788
Silver Contributor
Joined 7 years ago
User Widgets
Recent Discussions
Re: Delete cells with exactly three syllables
I think this is what you're looking to do (the 3 consonants don't have to be consecutive): =BYROW(words,LAMBDA(w,COUNTA(REGEXEXTRACT(w,"[^aeiou]",1,1))=3)) Regexextract checks for anything not a vowel (case insensitive) and COUNTA is there to count the consonants. The function is applied with BYROW. You could then filter for TRUE and delete as needed.Re: Double Thunking Works Wonders!
I've re-visited this task determined to pull this off with a bit more elegance. BlockMapλ is the result: // Function: BlockMapλ // Author: Patrick H. // Version: 1.0 // Published: 10/3/2025 // Repo: https://github.com/Patrick2788/Excel-Lambda/blob/main/BlockMap%CE%BB.xlsx // Description: BlockMapλ reshapes a matrix row or column-wise by specified // block size (depth x width) with optional parameters for // shaping by column and transposing anchors. BlockMapλ = LAMBDA( matrix, //Input matrix depth, //Height of each block width, //Width of each block [re_shape_by_column?], //Optional: If TRUE, reshapes column-wise; else row-wise [transpose_anchors?], //Optional: If TRUE, transpose row and col anchors; else normal anchors //Validate inputs Validateλ(matrix,depth,width,re_shape_by_column?, //Proceed LET( //----------Re-shape logic--------------------------------- x,ROUNDUP(ROWS(matrix)/depth,0), //Block rows y,ROUNDUP(COLUMNS(matrix)/width,0), //Block columns //Anchors i,SEQUENCE(x,,1,depth)*SEQUENCE(,y,1,0), j,SEQUENCE(,y,1,width)*SEQUENCE(x,,1,0), row_anchor,IF(ISOMITTED(transpose_anchors?),i,TRANSPOSE(i)), col_anchor,IF(ISOMITTED(transpose_anchors?),j,TRANSPOSE(j)), //Indices row_indices,TOCOL(row_anchor)+INT(SEQUENCE(,depth*width,0,1/(width))), col_indices,TOCOL(col_anchor)+TOROW(IFS(SEQUENCE(depth),SEQUENCE(,width,0,1))), //Output ReShapedMatrix,INDEX(matrix,row_indices,col_indices), output_matrix,IF(re_shape_by_column?,TRANSPOSE(ReShapedMatrix),ReShapedMatrix), output_matrix ))); //----------Error Handling--------------------------------- //Validate inputs for BlockMapλ. Validateλ = LAMBDA( matrix, depth, width, re_shape_by_column?, on_valid, //Halt if not an array. IF(NOT(TYPE(matrix)=64),"#MATRIX!", //Halt if re_shape_by_col? is TRUE and result would return #SPILL! error. IF((re_shape_by_column?)*(ROWS(matrix)*(COLUMNS(matrix))/(width*depth)^2>16384),"#SPILL-RISK!", //Halt if result would not be a clean re-shaping. IF((MOD(ROWS(matrix), depth) <> 0) + (MOD(COLUMNS(matrix),width)<>0),"#BLOCK MISMATCH!", //Halt if block size exceeds matrix dimensions or is text. IF(OR(AND(width=1,depth=1), depth>ROWS(matrix),width>COLUMNS(matrix), ISTEXT(width),ISTEXT(depth)),"#DIMENSIONS!", on_valid )))))Re: Delete cells with exactly three syllables
This is what it would take to solve in Excel. Pull together a list of the most common syllables found in English. Pull together a list of the most common words (or words most relevant to your data) Create all possible permutations (with repeats) from the list. Discard words which do not match words in your dictionary Discard words with precisely 3 syllables. My advice is to use the best version of Copilot at: https://copilot.microsoft.com/ You can fit about 12,000 characters in a single chat post.Re: Stacking The Beatles in Excel: An Exercise in 3D stacking
In an effort to turn my work into a generalized function, I'm coming to the conclusion a Lambda to stack and sort across a 3D sheet reference is not feasible. My function checks successfully for: - uniformity in column names in all sheets even if not in same order - valid 3D reference - the sheet where the function is used is not within the sheet stack - row and column limits of Excel not exceeded by stack estimate (before dupes removed, filtering) The main issue is Excel is incredibly shaky when stacking beyond 10,000+ rows across even 2 sheets. Stacking 10,000 elements is quick at .25 seconds. Doubling the rows leads to an immediate crash. I think this stacking could be a bit more reliable without the multiple checks but if the function will accept any 3D stack without inspection (and fail) then there are smarter options.Re: Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
I'm never satisfied so here's another go at it! //MooreAggλ is a generalized Moore (or Von Neumann) neighborhood aggregation //function in the polishing stages. Lifeλ = LAMBDA( config, //Board consisting of 1s and 0s generations,//Cellular automata states to simulate IF(generations=0,config, LET( neighbors,MooreAggλ(config,SUM,,1), keep,(config)*(neighbors=2)+(neighbors=3), //Alive with 2 or 3 neighbors revive,((config=0)*(neighbors=3)), //Dead with 3 neighbors Lifeλ( IF(keep+revive,1,0), generations-1))))Re: Finding Possible Matches to a Solution
The key to begin to solve the problem is to recognize the type of problem. This is the classic subset sum problem which can be handled by generating all possible combinations without repetition and then selecting only those rows which = target sum. SubsetSumλ = LAMBDA( integers, //array of numbers target_sum, //return rows which = this target LET( //Total numbers k, COUNT(integers), //Total combinations C, SUM(COMBIN(k, SEQUENCE(k))), //Generate bin array using BITRSHIFT to avoid limits of DEC2BIN bin, MOD(BITRSHIFT(SEQUENCE(C), SEQUENCE(, k, k - 1, -1)),2), //Swap binary for integers M, bin * TOROW(integers), //Check which rows = target sum keep, BYROW(M, SUM) = target_sum, //Deliver results deliver, FILTER(M, keep, "None"), deliver));Re: Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
I considered making a new discussion for this but since it's a bit off the beaten path for MTC, the best audience for it is probably in this discussion (and it relates to Conway with the need to find the Moore neighborhood and do some math). I recently created Terrainλ - a modular terrain analysis engine. The visuals are being handled with some creative conditional formatting. If any of this sounds interesting, please let me know here (or through a private message) and I'd be happy to provide an .xlsx to take for a spin. Thanks for taking a look! -Details- Terrainλ Return terrain analysis metrics by supplying square elevation matrices. Metrics: Aspect (radians), Classify, Curvature, Gradient Magnitude, Hillshade, Profile Curvature, Roughness, Slope (degrees), Slope (radians), ZTPI =Terrainλ(DEM,cellsize,function,[azimuth],[altitude],[z_factor])Re: Formula Challenge: The most efficient way to generate a Number Spiral (Ulam spiral) ?
One way to speed up the spiral is to use thunks/lazy evaluation all the way down: Spiralλ = LAMBDA(n, [start_at], LET( start_at, IF(ISOMITTED(start_at), 1, start_at), i, LAMBDA(SEQUENCE(n * 2 + 1, , n, -1)), j, LAMBDA(SEQUENCE(, n * 2 + 1, -n, 1)), x, LAMBDA(j() * SEQUENCE(n * 2 + 1, , 1, 0)), y, LAMBDA(i() * SEQUENCE(, n * 2 + 1, 1, 0)), a_, LAMBDA((ABS(x()) > ABS(y())) * (x() > 0)), b_, LAMBDA((ABS(x()) > ABS(y())) * (x() <= 0)), c_, LAMBDA((ABS(x()) <= ABS(y())) * (y() > 0)), d_, LAMBDA((ABS(x()) <= ABS(y())) * (y() <= 0)), a, LAMBDA(4 * x() ^ 2 - 3 * x() + y()), b, LAMBDA(4 * x() ^ 2 - x() - y()), c, LAMBDA(4 * y() ^ 2 - y() - x()), d, LAMBDA(4 * y() ^ 2 - 3 * y() + x()), result, start_at + IF(a_(), a(), IF(b_(), b(), IF(c_(), c(), d()))), result ) ); This generates a spiral with 51,854,401 elements in about 78 seconds.Re: Stacking The Beatles in Excel: An Exercise in 3D stacking
I like the first the one the best. It's very straightforward and efficient with the work mostly done with sequencing and some shaping functions. You may convince me to give IFS another look as it's never been one of my go-to functions. It is used smartly here in a way where the evaluating all arguments thing doesn't matter. With the first option I don't mind keeping errors and blanks if the function is speedy. Once the data is in better shape that can be dealt with if needed. Another point in regards to re-pivoting data. This sample data... ...is interesting to me because it's all text and can be analyzed a few different ways: unpivoted and aggregated, converting it to an adjacency matrix (Showing relational data by Instrument or by Person, for example), unpivot/sort/create tree map/sunburst chart, etc. That may be a topic for another discussion!Re: Stacking The Beatles in Excel: An Exercise in 3D stacking
Another go at this for 2025! **Edited - cleaned it up a bit more. StackNSortλ= LAMBDA(three_dimensional_ref, LET( stack, HSTACK(three_dimensional_ref), sorted, SORT(stack, , , 1), wrapped, WRAPCOLS(TOCOL(sorted, , 1), ROWS(sorted) * (SHEETS()-1)), header, TAKE(wrapped, 1), result, UNIQUE(FILTER(wrapped, (TAKE(wrapped, , 1) <> 0))), result ))Re: Best method to find variances between checkbook deposits and GL deposit transactions
There are elegant solutions to finding the subset sum of a given check book deposit total. However, the fact the checkbook date of 8/1, for example, may have GL deposits from multiple dates is the part that makes this difficult. If we disregard the GL dates in your example, both 2500 and 4500 have several ways to arrive at those totals. There must be something in those GL rows that would associate a given entry with the checkbook dates of 8/1 or 8/2, for example.
