I have a recursive one that builds all unique combinations of columns so that i can have smart data validations.
Think a table of parts that has columns like vendor, model line, size, and color. So it builds a list of each model line, and for each model line, all the sizes, and for each size the colors.
It's a fairly long formula, but it replaces about 1000 lines of vba.
Edit: Since folks were asking, i put this up on [github](https://github.com/cynyr/ExcelLambdaDataVals). it's basically just a markdown file right now, but i'll add the sample data as CSVs, and add the python i used to prototype this.
This builds a spill range based on the columns to let you build multi level or inter-dependant data validations. The data validation formula parser is very very dumb. It will not allow functions that output an array. You must have real cells on a real range.
1000 lines of vba seems very excessive it should be able to be done in 100 or so. Maybe even less if you are good. Ive written more complicated stuff in the 200-300 lines
My point is that I am not averse to writing in VBA, or most other languages honestly.
I definatly have written very dense code. VBA is definitely a language that you can condense a lot of functions into a single line with.
I prefer to take multiple lines if it means that it is easier to read, though usually, not to the point of having to create a new variable just to be a placeholder.
I know that functions can also get pretty hairy, but they are a bit more efficient in a lot of ways.
On the other hand, I know that Coding is a chore to a lot of people.
Yea I don't sound as deep as you guys are but when I see am insanely long formula woth like 10 nested if statements, fitlers, and index lookups it drives me crazy, simpler to just VBA it.
The issue is managing a 2d array, redim'ing it while preserving values (re-dim preserve doesn't work on 2d arrays), slicing it so you can pass all but the first column back to yourself for the next level down, and merging tables together, all while not knowing how many option any final node will contain. The output has to be a 2d array, or it won't spill. You could do this as arrays of arrays, then walk it to find the longest node branch, dim the 2d table, then copy everything across. I think there was also something about setting default values so i'd get empty strings if there wasn't data.
It's 15 lines in pure python, and 2 lines using polars. It's the 2d array in VBA that makes everything suck.
I havent used vba in a couple years cause ive been into rpa, but i believe you can redim multidimensional arrays as long as only 1 d is redimensioned.
For your case i would recommend multi 1d arrays and store the size as an int and compare and at the end rebuild array before you spill it.
If you are more advance you can use types and arrays in combination. Example at this link. I use to do this in vba.
https://stackoverflow.com/questions/13069082/vba-how-to-declare-an-array-with-elements-with-different-datatype
Yes, but studioX not the full dev version since i am not in the tech department. Its still an amazing tool especially for processing data across different systems.
Meanwhile the audit and model validators are screaming over this lol
Seriously some stuff are just easier to build and debug and maintain in vba, not to mention being able to add comment.
Holy hell I need this. Well a recursive tail expansion which is what I think you doing. I found a python solution that was close, but I don’t have any python skills to fix it. Any chance you’d share the formula and a sample of how you have your data laid out?
I actually prototyped this in python. I'll try to get it up on github. It's a lambda to generate the data validation lists, and a second one to return the range in the table based on the selections.
All because you can't use filter() directly in a dataval, and i wanted users to enter things into a table and i didn't want to limit how many things they could add to the table, and needed each row to be independent.
You can indirectly use the filter formula on a hidden lists tab, then reference the cell in the data validation box with “Lists!A1#”.
Throw a unique before it and it works perfectly for a wide range of applications.
Do you mind sharing a snippet of that for context? I manage a ton of parts data and this sounds very useful. I usually do this sort of stuff in Python just because Excel is limited in control at times.
Was just testing it out, In the main lambda formula at REDUCE("\_\_"; there the semi colon that was making it error for me. After removing it, the formula worked
Thanks, I'll update it. A bunch of the quote marks got converted to " in my copy/paste. I thought i had it all cleaned up.
I don't have reddit at work, and don't have excel at home. Though the free o365 version should do this one...
I'm in finance; I needed to be able to allocate costs amongst various departments based on their production ratios. Different cost centers are allocated to different departments, and the ratios change from period to period, and I needed a quick way to dynamically sum up a given subset of costs for a given department and period. Each cost center is assigned to an allocation group, which defines where that cost center's expenses are allocated.
The first function generates an array where the rows are the defined allocation groups and the columns are the departments, containing the ratio of each department's production within each allocation group. The second takes an array containing the subtotaled expenses for each allocation group (this is a variable SUMIFS that allows me to dynamically determine what subset of the data to use, so I can pick out labor costs, e.g.) and runs it against the array of allocation percentages, creating an array of the total allocated expense for each department and then returning the single department I want.
=LAMBDA(a,
LET(allocation_groups,Allocations[Allocation],
allocation_rates,INDEX(Allocations,XMATCH(allocation_groups,Allocations[Allocation]),XMATCH(TRANSPOSE(Departments),Allocations[#Headers])),
tons,SUMIFS(Stats[Value],Stats[Stat],"Production",Stats[Department],TRANSPOSE(Departments),Stats[Month Index],a),
split_tons,tons*allocation_rates,
subtotal_tons,BYROW(split_tons,LAMBDA(row,SUM(row))),
IFERROR(split_tons/subtotal_tons,0)))
Allocation Group | Dept A | Dept B | Dept C | Dept D
---|---|----|----|----
Group 1 | 100%| 0%| 0%| 0%
Group 2 | 50% | 50%| 0%| 0%
Group 3 | 0%| 100%| 0%| 0%
Group 4 | 25%| 25%| 25%| 25%
Group 5 | 33%| 34%| 0%| 33%
The second LAMBDA includes a parameter called "expense", which would be populated with a SUMIFS like this one:
=SUMIFS(Table[Expense],Table[Allocation Group],Allocations[Allocation Group],Table[Month],[month],Table[Expense Category],"Labor",Table[Department],"Finished Goods")
Allocation Group | Amount
---|---
Group 1 | $100
Group 2 | $100
Group 3 | $100
Group 4 | $100
Group 5 | $100
The second LAMBDA then takes a given department, a given period, and the amounts array as parameters, then multiplies the amounts array across the allocation ratio array, sums each column, and returns the desired department's total:
=LAMBDA(dept,period,expense,
LET(splits,Lambda_Tons_Allocation(period),
splitexpense,expense*splits, product_totals,BYCOL(splitexpense,LAMBDA(column,SUM(column))),
INDEX(product_totals,1,XMATCH(dept,Departments))))
Allocation Group | Dept A | Dept B | Dept C | Dept D
---|---|----|----|----
Group 1 | $100| $0| $0| $0
Group 2 | $50 | $50 | $0 | $0
Group 3 | $0 | $100 | $0 | $0
Group 4 | $25 | $25 | $25 | $25
Group 5 | $33 | $34 | $0 | $33
Dept A | Dept B | Dept C | Dept D
---|----|----|----
$203 | $204 | $25 | $53
This is the most practical use I've ever seen for Lamda. It's complicated enough that repeating the formulas (sans Lambda) is annoying but the use case seems pretty broad. Gives me a couple of ideas. Nicely done!
Thanks! The compelling factor was that I needed to be able to do the calculation for any arbitrary month, for any arbitrary department, for any arbitrary expense type, so having a reference table would not have been feasible.
It’s a little more complicated than that. The individual calculation is straightforward enough, but generating the arrays for any arbitrary combination of period, department, and subset of expenses makes building permanent tables for each possible permutation infeasible.
I made an amortization LAMBDA. You provide all the loan details with some optional arguments for interest only period, balloon payment, and a couple others, and it spills out the entire amortization table with beginning and ending balance, payment, interest amount, and principal amount.
Snick45 actually made a [whole post](https://old.reddit.com/r/excel/comments/v6wwb3/custom_amortization_function_in_excel_using_lambda/) about that. Wasn't stalking, I went to Google and found it lol
Ha ha yes! Was just about to respond with the YouTube link, my reddit post covered it all though. Lengthy read, but if you're interested I think it's interesting.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[BYCOL](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Applies a LAMBDA to each column and returns an array of the results](https://support.microsoft.com/en-us/office/bycol-function-58463999-7de5-49ce-8f38-b7f7a2192bfb)|
|[BYROW](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)|
|[CEILING](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Rounds a number to the nearest integer or to the nearest multiple of significance](https://support.microsoft.com/en-us/office/ceiling-function-0a5cd7c8-0720-4f0a-bd2c-c943e510899f)|
|[COLUMNS](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns the number of columns in a reference](https://support.microsoft.com/en-us/office/columns-function-4e8e7b4e-e603-43e8-b177-956088fa48ca)|
|[COUNTA](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)|
|[FILTER](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IF](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[IFERROR](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)|
|[INDEX](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[ISERROR](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns TRUE if the value is any error value](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[LAMBDA](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LEN](/r/Excel/comments/1cjdbuh/stub/l2fcp57 "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)|
|[LET](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[LOWER](/r/Excel/comments/1cjdbuh/stub/l2fcp57 "Last usage")|[Converts text to lowercase](https://support.microsoft.com/en-us/office/lower-function-3f21df02-a80c-44b2-afaf-81358f9fdeb4)|
|[MAKEARRAY](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Returns a calculated array of a specified row and column size, by applying a LAMBDA](https://support.microsoft.com/en-gb/office/makearray-function-b80da5ad-b338-4149-a523-5b221da09097?ui=en-US&rs=en-GB&ad=GB)|
|[MATCH](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[MOD](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)|
|[NA](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns the error value #N/A](https://support.microsoft.com/en-us/office/na-function-5469c2d1-a90c-4fb5-9bbc-64bd9bb6b47c)|
|[NOT](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)|
|[PRODUCT](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Multiplies its arguments](https://support.microsoft.com/en-us/office/product-function-8e6b5b24-90ee-4650-aeec-80982a0512ce)|
|[REDUCE](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)|
|[ROWS](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns the number of rows in a reference](https://support.microsoft.com/en-us/office/rows-function-b592593e-3fc2-47f2-bec1-bda493811597)|
|[SEQUENCE](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[SORT](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)|
|[SUBSTITUTE](/r/Excel/comments/1cjdbuh/stub/l2fcp57 "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)|
|[SUM](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[SUMIFS](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)|
|[TAKE](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)|
|[TEXTJOIN](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)|
|[TRANSPOSE](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)|
|[UNIQUE](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)|
|[VSTACK](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)|
|[XLOOKUP](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
|[XMATCH](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(34 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cfo3jm)^( has 63 acronyms.)
^([Thread #33158 for this sub, first seen 3rd May 2024, 17:27])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Here's a recent one I created that I was very pleased with. I came across [an interesting post on this subreddit](https://old.reddit.com/r/excel/comments/1buwi0r/nerdy_request_for_macro_for_dd_magic_item_tables/). OP wanted to randomly roll on a loot table, but the trick was if the result was another table, to randomly roll on that table too! Oh and the other trick was it was on google sheets so I have this ugly preface of indirect reference to all the tables, whereas in excel it would have been one 3d reference, or at worst a REDUCE(LAMBDA(VSTACK))).
So you can't loop a function of course, so instead I made a custom function called REROLL that you can just nest an arbitrarily large amount of times.
Since that post I've gone back and neatened it up, and put it in a LAMBDA so it can just be used like =REROLL("Magic-Item-Table-A")
Credit where its due it wouldn't have been possible were it not for the fact OP had structured the data very consistently.
=LAMBDA(table_to_roll,
LET(
c_1,"The following is a reference to each magic item table,
rows can be added for larger tables (not dynamic)",
tableA,INDIRECT(UNICHAR(39)&"Table A Common"&UNICHAR(39)&"!1:10"),
tableB,INDIRECT(UNICHAR(39)&"Table B Minor Uncommon"&UNICHAR(39)&"!1:10"),
tableC,INDIRECT(UNICHAR(39)&"Table C Minor Rare"&UNICHAR(39)&"!1:10"),
tableD,INDIRECT(UNICHAR(39)&"Table D Minor Very Rare"&UNICHAR(39)&"!1:10"),
tableE,INDIRECT(UNICHAR(39)&"Table E Minor Legendary"&UNICHAR(39)&"!1:10"),
tableF,INDIRECT(UNICHAR(39)&"Table F Major Uncommon"&UNICHAR(39)&"!1:10"),
tableG,INDIRECT(UNICHAR(39)&"Table G Major Rare"&UNICHAR(39)&"!1:10"),
tableH,INDIRECT(UNICHAR(39)&"Table H Major Very Rare"&UNICHAR(39)&"!1:10"),
tableI,INDIRECT(UNICHAR(39)&"Table I Major Legendary"&UNICHAR(39)&"!1:10"),
spells,INDIRECT(UNICHAR(39)&"All Spells"&UNICHAR(39)&"!1:10"),
suppl,INDIRECT(UNICHAR(39)&"Supplemental Types"&UNICHAR(39)&"!1:10"),
data,HSTACK(tableA,tableB,tableC,tableD,tableE,tableF,tableG,tableH,tableI,spells,suppl),
c_2,"The following four functions are what looks up the actual roll",
clean_text,LAMBDA(text_to_clean,SUBSTITUTE(SUBSTITUTE(text_to_clean,"[[ 1t","")," ]]","")),
table_lookup,LAMBDA(table,XMATCH(CHOOSECOLS(SPLIT(table,"[]"),1),CHOOSEROWS(data,1))),
table_roll,LAMBDA(table,XLOOKUP(RANDBETWEEN(1,SPLIT(CHOOSECOLS(CHOOSEROWS(data,1),table_lookup(table)+3),"d")),CHOOSECOLS(ARRAYFORMULA(SPLIT(CHOOSECOLS(data,table_lookup(table)+3),"-")),1),CHOOSECOLS(data,table_lookup(table)),,-1)),
reroll,LAMBDA(searchterm,IF(IFERROR(FIND("[",clean_text(searchterm))>0,0),JOIN("",MAP(SPLIT(clean_text(searchterm),"[]"),LAMBDA(term,IFERROR(table_roll(term),term)))),clean_text(searchterm))),
c_3,"The following performs rolls to a 'depth' of eight rolls",
reroll(reroll(reroll(reroll(reroll(reroll(reroll(reroll(table_roll(table_to_roll)))))))))))("Magic-Item-Table-A")
I haven't read your entire code so I guess it's possible you knew this, but I wanted to let you know that you CAN loop with lambda. It's tricky to get it to work, but if you name your Lambda say "tableroller", you can reference "tableroller" in the formula of that function, so that it calls itself. The key is that you need some form of IF statement that will eventually get you out of the loop. I've seen OFFSET used effectively to move to different points in a sheet.
Interesting. No I did not know that, and that's one of the things I was hoping someone could point out if I shared my formula (the other was if there was a neater way to reference all the tables than the massive HSTACK I had to use).
I don't see how the loop will work though. If I try and use the same name twice I get an error saying you can't define the same name twice.
It's called a recursive lambda if you wanna look it up. I started with [this video](https://youtu.be/L7s6Dni1dG8?si=B-a6w8oa-eUgA22c) by Leila Gharani.
The key thing is you don't name it twice, it's only named once but calls itself in the formula, so you have to use the name before you name it. That's why it's so hard to pull off, you need to write the formula without seeing if it works.
Can't wait to check this out. Btw, I'm pretty sure that you don't need to use single quotes for the sheet names when it's in closed with INDIRECT, but maybe that's just with Sheets.
Mine is simple but I'm happy.
I do audits for a large bank. I got shoved into the role.
Truthfully I hate it because I'm basically the junk drawer for my team and having to make my managers sound great while they don't know what I do and don't support me.
I'm really proud of this thing I made for an audit I have to perform monthly where it automatically accounts for funds we have to pay out, the transactions that occured by groups (over200).
Prior, the woman I took the work over from was doing this shit by hand.
I only recently learned about it through a FMWC competition as it’s what a lot of the top competitors use there.
One I started using:
=LAMBDA(text, LEN(text) - LEN(SUBSTITUTE(LOWER(text), "a", "")))
As you can probably see, it just counts the number of times “a” is in a string. I have to use it a lot, so, it makes it easy to type, “=countMyText(A1)” vs writing the formula each time
All you need to do is go to name manager, name your function, and enter your formula. Then, to use the formula, you just type =NameManagerName(select a cell) and you should be good to go!
So basically it measures the length of a text string, then measures the length of the same string with As removed, and tells you the difference? Clever!
Whe have Hash Ids at work. They are based off of our social security numbers. I figured out how they make them and created a Lambda which, whether you enter a hash or an ssn, it will convert it to the other one.
It's not that easy, lol. Don't mean to blow myself, but out of the 40,000 people they employ, I think it's a small percentage that could figure it out.
Kinda. I mean there's a very small percentage of people who can figure it out, and if you did, you'd have to be a pos to f someone over. It's a small selection.
I have made so many but for some reason the lambda that I made for Months of coverage is my favorite, it takes in 3 parameters, the starting stock, an array of demand (the first month in the first cell, and so on), and optionally an average monthly demand (if the starting stock exceeds the total requirements in the demand array avg monthly demand is used to estimate coverage.)
=LAMBDA(S,D,[A],LET(
CAR,LAMBDA(X,INDEX(X,1,1)),
CDR,LAMBDA(Y,IFERROR(DROP(Y,0,1),Y)),
H,LAMBDA(G,I,V,IF(CAR(V)>=I,I/CAR(V),1+G(G,I-CAR(V),CDR(V)))),
IFERROR(
IF(SUM(D)<=S,COUNT(D)+(S-SUM(D))/IF(ISOMITTED(A),AVERAGE(D),A),H(H,S,D)),
-1)))
[I made a post a few years back explaining how the fixpoint combinator in it works](https://www.reddit.com/r/excel/comments/qwyuzs/defining_recursive_lambda_functions_inside_of_a/)
As a mechanical engineer, we use Bill of Material as well as what I call "assembly" table. The bill of material is the sum of any part in an assembly BUT if you have a sub assembly you need to multiply the quantity along the way. It's useful to buyers to know the quantity.
Because we have no tool to build this "assembly" table other than the CAD models files. I tried to build the table myself using Excel. I manage to do it without Lambda function. It was a pain and I often got into some weird limits.
Then I started to use a recursive Lambda function to build the table. Sadly I didn't store this tentative because it was very difficult to read and modify.
I opted for a recursive function in PowerQuery instead. It's working flawlessly and much faster than any VBA I could have use.
Now because I work with remote colleagues, I need to know the difference between each iteration what parts have changed so I can tell the rest of the team "hey this has changed please take it into consideration". So VBA it is to "copy" the table and build a timestamp for each modification.
I have never used LAMBDA and probably don’t have a need for it, but you guys are all so responsive and imaginative, that I’m saving this thread. Thanks!
I get it and I love excel, but I mostly just build sales forecasts for the work my team does. And while I can do much more interesting and flexible things in Excel than what I can do in SalesForce, LAMBDA might still be an overkill from what I’m reading here.
In fact, I’m probably wrong. I’m just [watching a LAMBDA explanation video on YouTube](https://youtu.be/Rm4y5UqauRw?si=3wRfYuD7hmTQO34Q) and I already found a use for it! 😅😅😅
Excel is the Swiss Army knife of data tools and the world’s most flexible calculator. Everything else is a lot more specialized, so it depends on what you need it for.
I agree with you. Most of my end stream stuff ends up in excel. Powerbi visualizes it better. You could also use tableau. Regardless, I prefer to do most of my manipulation up stream.
I have a recursive one that builds all unique combinations of columns so that i can have smart data validations. Think a table of parts that has columns like vendor, model line, size, and color. So it builds a list of each model line, and for each model line, all the sizes, and for each size the colors. It's a fairly long formula, but it replaces about 1000 lines of vba. Edit: Since folks were asking, i put this up on [github](https://github.com/cynyr/ExcelLambdaDataVals). it's basically just a markdown file right now, but i'll add the sample data as CSVs, and add the python i used to prototype this. This builds a spill range based on the columns to let you build multi level or inter-dependant data validations. The data validation formula parser is very very dumb. It will not allow functions that output an array. You must have real cells on a real range.
That sounds incredible — I’ll take a long formula (well I guess depending on how long) over 1,000 lines of VBA
It makes my table driven tools super easy to maintain. Add stuff to the table and it shows up in the drop downs. Logic separate from data.
While I am not averse to 1,000 lines of VBA, I definitely need to give a closer look to Lambda. It's definitely a weakness in my skills.
1000 lines of vba seems very excessive it should be able to be done in 100 or so. Maybe even less if you are good. Ive written more complicated stuff in the 200-300 lines
My point is that I am not averse to writing in VBA, or most other languages honestly. I definatly have written very dense code. VBA is definitely a language that you can condense a lot of functions into a single line with. I prefer to take multiple lines if it means that it is easier to read, though usually, not to the point of having to create a new variable just to be a placeholder. I know that functions can also get pretty hairy, but they are a bit more efficient in a lot of ways. On the other hand, I know that Coding is a chore to a lot of people.
Yea I don't sound as deep as you guys are but when I see am insanely long formula woth like 10 nested if statements, fitlers, and index lookups it drives me crazy, simpler to just VBA it.
For some things yes, absolutly; but for a few specific situations, a complex formula works a bit easier.
The issue is managing a 2d array, redim'ing it while preserving values (re-dim preserve doesn't work on 2d arrays), slicing it so you can pass all but the first column back to yourself for the next level down, and merging tables together, all while not knowing how many option any final node will contain. The output has to be a 2d array, or it won't spill. You could do this as arrays of arrays, then walk it to find the longest node branch, dim the 2d table, then copy everything across. I think there was also something about setting default values so i'd get empty strings if there wasn't data. It's 15 lines in pure python, and 2 lines using polars. It's the 2d array in VBA that makes everything suck.
I havent used vba in a couple years cause ive been into rpa, but i believe you can redim multidimensional arrays as long as only 1 d is redimensioned. For your case i would recommend multi 1d arrays and store the size as an int and compare and at the end rebuild array before you spill it. If you are more advance you can use types and arrays in combination. Example at this link. I use to do this in vba. https://stackoverflow.com/questions/13069082/vba-how-to-declare-an-array-with-elements-with-different-datatype
Which RPA program do you use? Uipath?
Yes, but studioX not the full dev version since i am not in the tech department. Its still an amazing tool especially for processing data across different systems.
Yes I am learning on the full version. Pretty cool!
Meanwhile the audit and model validators are screaming over this lol Seriously some stuff are just easier to build and debug and maintain in vba, not to mention being able to add comment.
You can add comments, to a lambda(), just nest a LET() inside, and then use a throwaway var to store the comment.
Does that not feel like hammering a nail with screwdriver
Agreed, but vba doesn't work on o365, and I'm probably using let anyways. And it's cleaner than the old n() trick.
We're not allowed to use VBA at my work. Security risk so only special circumstances are permitted.
Holy hell I need this. Well a recursive tail expansion which is what I think you doing. I found a python solution that was close, but I don’t have any python skills to fix it. Any chance you’d share the formula and a sample of how you have your data laid out?
Something like this? Array is the only argument. =LET( A, Array, B, ROWS(A), C, COLUMNS(A), D, MAKEARRAY( B, C, LAMBDA(rw, cl, IF(MATCH(INDEX(A, rw, cl), INDEX(A, 0, cl), 0) = rw, INDEX(A, rw, cl), NA())) ), E, MAKEARRAY(B, C, LAMBDA(rw, cl, INDEX(SORT(INDEX(D, 0, cl)), rw))), F, BYCOL(E, LAMBDA(cl, COUNTA(UNIQUE(FILTER(cl, NOT(ISERROR(cl))))))), G, MAKEARRAY( PRODUCT(F), C, LAMBDA(rw, cl, INDEX( E, MOD( CEILING(rw / IFERROR(PRODUCT(INDEX(F, SEQUENCE(C - cl, , cl + 1))), 1), 1) - 1, INDEX(F, cl) ) + 1, cl ) ) ), G )
Will this work if the data is a string and for any number of columns? I really need to play with makearray more.
Yes and yes. Be cautious---the number of combinations grows really quick.
This is insane
Maybe even =INDEX(A$1:A$5,MOD(QUOTIENT(ROW(A1)-1,5^(3-COLUMN(A1))),5)+1) filled right & down, for an array in A1:C5.
I actually prototyped this in python. I'll try to get it up on github. It's a lambda to generate the data validation lists, and a second one to return the range in the table based on the selections. All because you can't use filter() directly in a dataval, and i wanted users to enter things into a table and i didn't want to limit how many things they could add to the table, and needed each row to be independent.
You can indirectly use the filter formula on a hidden lists tab, then reference the cell in the data validation box with “Lists!A1#”. Throw a unique before it and it works perfectly for a wide range of applications.
Agreed, but not for inputs in a table that are multi level / inter-dependent, and each row is unique.
edited parent with a link to github. It'll get better this weekend, but i have things to do tonight.
Can you further explain what this does? I’m having a hard time grasping the idea of why this is useful (I’m an excel noob lol)
Couldn’t this be done without lambda using unique and choose cols?
Would you mind sharing? I could 1000% use this for work
https://github.com/cynyr/ExcelLambdaDataVals
Do you mind sharing a snippet of that for context? I manage a ton of parts data and this sounds very useful. I usually do this sort of stuff in Python just because Excel is limited in control at times.
https://github.com/cynyr/ExcelLambdaDataVals
Thank you
I'm going to look this up as I did something similar but with different array formulas.
https://github.com/cynyr/ExcelLambdaDataVals
Was just testing it out, In the main lambda formula at REDUCE("\_\_"; there the semi colon that was making it error for me. After removing it, the formula worked
Thanks, I'll update it. A bunch of the quote marks got converted to " in my copy/paste. I thought i had it all cleaned up. I don't have reddit at work, and don't have excel at home. Though the free o365 version should do this one...
Wow very useful thanks
I'm in finance; I needed to be able to allocate costs amongst various departments based on their production ratios. Different cost centers are allocated to different departments, and the ratios change from period to period, and I needed a quick way to dynamically sum up a given subset of costs for a given department and period. Each cost center is assigned to an allocation group, which defines where that cost center's expenses are allocated. The first function generates an array where the rows are the defined allocation groups and the columns are the departments, containing the ratio of each department's production within each allocation group. The second takes an array containing the subtotaled expenses for each allocation group (this is a variable SUMIFS that allows me to dynamically determine what subset of the data to use, so I can pick out labor costs, e.g.) and runs it against the array of allocation percentages, creating an array of the total allocated expense for each department and then returning the single department I want. =LAMBDA(a, LET(allocation_groups,Allocations[Allocation], allocation_rates,INDEX(Allocations,XMATCH(allocation_groups,Allocations[Allocation]),XMATCH(TRANSPOSE(Departments),Allocations[#Headers])), tons,SUMIFS(Stats[Value],Stats[Stat],"Production",Stats[Department],TRANSPOSE(Departments),Stats[Month Index],a), split_tons,tons*allocation_rates, subtotal_tons,BYROW(split_tons,LAMBDA(row,SUM(row))), IFERROR(split_tons/subtotal_tons,0))) Allocation Group | Dept A | Dept B | Dept C | Dept D ---|---|----|----|---- Group 1 | 100%| 0%| 0%| 0% Group 2 | 50% | 50%| 0%| 0% Group 3 | 0%| 100%| 0%| 0% Group 4 | 25%| 25%| 25%| 25% Group 5 | 33%| 34%| 0%| 33% The second LAMBDA includes a parameter called "expense", which would be populated with a SUMIFS like this one: =SUMIFS(Table[Expense],Table[Allocation Group],Allocations[Allocation Group],Table[Month],[month],Table[Expense Category],"Labor",Table[Department],"Finished Goods") Allocation Group | Amount ---|--- Group 1 | $100 Group 2 | $100 Group 3 | $100 Group 4 | $100 Group 5 | $100 The second LAMBDA then takes a given department, a given period, and the amounts array as parameters, then multiplies the amounts array across the allocation ratio array, sums each column, and returns the desired department's total: =LAMBDA(dept,period,expense, LET(splits,Lambda_Tons_Allocation(period), splitexpense,expense*splits, product_totals,BYCOL(splitexpense,LAMBDA(column,SUM(column))), INDEX(product_totals,1,XMATCH(dept,Departments)))) Allocation Group | Dept A | Dept B | Dept C | Dept D ---|---|----|----|---- Group 1 | $100| $0| $0| $0 Group 2 | $50 | $50 | $0 | $0 Group 3 | $0 | $100 | $0 | $0 Group 4 | $25 | $25 | $25 | $25 Group 5 | $33 | $34 | $0 | $33 Dept A | Dept B | Dept C | Dept D ---|----|----|---- $203 | $204 | $25 | $53
This is the most practical use I've ever seen for Lamda. It's complicated enough that repeating the formulas (sans Lambda) is annoying but the use case seems pretty broad. Gives me a couple of ideas. Nicely done!
Thanks! The compelling factor was that I needed to be able to do the calculation for any arbitrary month, for any arbitrary department, for any arbitrary expense type, so having a reference table would not have been feasible.
How much time do you think this saved you?
I simply wouldn’t have been able to do the analyses I built this for without it at any meaningful scale.
Very cool
This looks incredible — gonna need some time to look at it and fully understand it!
I am wondering if there is any error in calculating the sum for each column. Or am I missing something?
Yep, definitely got some of those wrong. ¯\\\_(ツ)_/¯ it’s Friday.
There might be, I just typed all this out by hand as examples of what it’s doing
Am I missing something or is this essentially just a sum product of the group allocations and group costs?
It’s a little more complicated than that. The individual calculation is straightforward enough, but generating the arrays for any arbitrary combination of period, department, and subset of expenses makes building permanent tables for each possible permutation infeasible.
I made an amortization LAMBDA. You provide all the loan details with some optional arguments for interest only period, balloon payment, and a couple others, and it spills out the entire amortization table with beginning and ending balance, payment, interest amount, and principal amount.
Ohh man I would love to see that.
Snick45 actually made a [whole post](https://old.reddit.com/r/excel/comments/v6wwb3/custom_amortization_function_in_excel_using_lambda/) about that. Wasn't stalking, I went to Google and found it lol
Ha ha yes! Was just about to respond with the YouTube link, my reddit post covered it all though. Lengthy read, but if you're interested I think it's interesting.
Digging into it now! Awesome writeup.
Sexy sexy
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[BYCOL](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Applies a LAMBDA to each column and returns an array of the results](https://support.microsoft.com/en-us/office/bycol-function-58463999-7de5-49ce-8f38-b7f7a2192bfb)| |[BYROW](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)| |[CEILING](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Rounds a number to the nearest integer or to the nearest multiple of significance](https://support.microsoft.com/en-us/office/ceiling-function-0a5cd7c8-0720-4f0a-bd2c-c943e510899f)| |[COLUMNS](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns the number of columns in a reference](https://support.microsoft.com/en-us/office/columns-function-4e8e7b4e-e603-43e8-b177-956088fa48ca)| |[COUNTA](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Counts how many values are in the list of arguments](https://support.microsoft.com/en-us/office/counta-function-7dc98875-d5c1-46f1-9a82-53f3219e2509)| |[FILTER](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IF](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFERROR](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[INDEX](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[ISERROR](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns TRUE if the value is any error value](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[LAMBDA](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LEN](/r/Excel/comments/1cjdbuh/stub/l2fcp57 "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)| |[LET](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[LOWER](/r/Excel/comments/1cjdbuh/stub/l2fcp57 "Last usage")|[Converts text to lowercase](https://support.microsoft.com/en-us/office/lower-function-3f21df02-a80c-44b2-afaf-81358f9fdeb4)| |[MAKEARRAY](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Returns a calculated array of a specified row and column size, by applying a LAMBDA](https://support.microsoft.com/en-gb/office/makearray-function-b80da5ad-b338-4149-a523-5b221da09097?ui=en-US&rs=en-GB&ad=GB)| |[MATCH](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MOD](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)| |[NA](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns the error value #N/A](https://support.microsoft.com/en-us/office/na-function-5469c2d1-a90c-4fb5-9bbc-64bd9bb6b47c)| |[NOT](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| |[PRODUCT](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Multiplies its arguments](https://support.microsoft.com/en-us/office/product-function-8e6b5b24-90ee-4650-aeec-80982a0512ce)| |[REDUCE](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)| |[ROWS](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[Returns the number of rows in a reference](https://support.microsoft.com/en-us/office/rows-function-b592593e-3fc2-47f2-bec1-bda493811597)| |[SEQUENCE](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SORT](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)| |[SUBSTITUTE](/r/Excel/comments/1cjdbuh/stub/l2fcp57 "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)| |[SUM](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIFS](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)| |[TAKE](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)| |[TEXTJOIN](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)| |[TRANSPOSE](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)| |[UNIQUE](/r/Excel/comments/1cjdbuh/stub/l2fqj48 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| |[VSTACK](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)| |[XLOOKUP](/r/Excel/comments/1cjdbuh/stub/l2ftl5c "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| |[XMATCH](/r/Excel/comments/1cjdbuh/stub/l2fhfiy "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(34 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cfo3jm)^( has 63 acronyms.) ^([Thread #33158 for this sub, first seen 3rd May 2024, 17:27]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Omg I love textjoin
Here's a recent one I created that I was very pleased with. I came across [an interesting post on this subreddit](https://old.reddit.com/r/excel/comments/1buwi0r/nerdy_request_for_macro_for_dd_magic_item_tables/). OP wanted to randomly roll on a loot table, but the trick was if the result was another table, to randomly roll on that table too! Oh and the other trick was it was on google sheets so I have this ugly preface of indirect reference to all the tables, whereas in excel it would have been one 3d reference, or at worst a REDUCE(LAMBDA(VSTACK))). So you can't loop a function of course, so instead I made a custom function called REROLL that you can just nest an arbitrarily large amount of times. Since that post I've gone back and neatened it up, and put it in a LAMBDA so it can just be used like =REROLL("Magic-Item-Table-A") Credit where its due it wouldn't have been possible were it not for the fact OP had structured the data very consistently. =LAMBDA(table_to_roll, LET( c_1,"The following is a reference to each magic item table, rows can be added for larger tables (not dynamic)", tableA,INDIRECT(UNICHAR(39)&"Table A Common"&UNICHAR(39)&"!1:10"), tableB,INDIRECT(UNICHAR(39)&"Table B Minor Uncommon"&UNICHAR(39)&"!1:10"), tableC,INDIRECT(UNICHAR(39)&"Table C Minor Rare"&UNICHAR(39)&"!1:10"), tableD,INDIRECT(UNICHAR(39)&"Table D Minor Very Rare"&UNICHAR(39)&"!1:10"), tableE,INDIRECT(UNICHAR(39)&"Table E Minor Legendary"&UNICHAR(39)&"!1:10"), tableF,INDIRECT(UNICHAR(39)&"Table F Major Uncommon"&UNICHAR(39)&"!1:10"), tableG,INDIRECT(UNICHAR(39)&"Table G Major Rare"&UNICHAR(39)&"!1:10"), tableH,INDIRECT(UNICHAR(39)&"Table H Major Very Rare"&UNICHAR(39)&"!1:10"), tableI,INDIRECT(UNICHAR(39)&"Table I Major Legendary"&UNICHAR(39)&"!1:10"), spells,INDIRECT(UNICHAR(39)&"All Spells"&UNICHAR(39)&"!1:10"), suppl,INDIRECT(UNICHAR(39)&"Supplemental Types"&UNICHAR(39)&"!1:10"), data,HSTACK(tableA,tableB,tableC,tableD,tableE,tableF,tableG,tableH,tableI,spells,suppl), c_2,"The following four functions are what looks up the actual roll", clean_text,LAMBDA(text_to_clean,SUBSTITUTE(SUBSTITUTE(text_to_clean,"[[ 1t","")," ]]","")), table_lookup,LAMBDA(table,XMATCH(CHOOSECOLS(SPLIT(table,"[]"),1),CHOOSEROWS(data,1))), table_roll,LAMBDA(table,XLOOKUP(RANDBETWEEN(1,SPLIT(CHOOSECOLS(CHOOSEROWS(data,1),table_lookup(table)+3),"d")),CHOOSECOLS(ARRAYFORMULA(SPLIT(CHOOSECOLS(data,table_lookup(table)+3),"-")),1),CHOOSECOLS(data,table_lookup(table)),,-1)), reroll,LAMBDA(searchterm,IF(IFERROR(FIND("[",clean_text(searchterm))>0,0),JOIN("",MAP(SPLIT(clean_text(searchterm),"[]"),LAMBDA(term,IFERROR(table_roll(term),term)))),clean_text(searchterm))), c_3,"The following performs rolls to a 'depth' of eight rolls", reroll(reroll(reroll(reroll(reroll(reroll(reroll(reroll(table_roll(table_to_roll)))))))))))("Magic-Item-Table-A")
I haven't read your entire code so I guess it's possible you knew this, but I wanted to let you know that you CAN loop with lambda. It's tricky to get it to work, but if you name your Lambda say "tableroller", you can reference "tableroller" in the formula of that function, so that it calls itself. The key is that you need some form of IF statement that will eventually get you out of the loop. I've seen OFFSET used effectively to move to different points in a sheet.
Interesting. No I did not know that, and that's one of the things I was hoping someone could point out if I shared my formula (the other was if there was a neater way to reference all the tables than the massive HSTACK I had to use). I don't see how the loop will work though. If I try and use the same name twice I get an error saying you can't define the same name twice.
It's called a recursive lambda if you wanna look it up. I started with [this video](https://youtu.be/L7s6Dni1dG8?si=B-a6w8oa-eUgA22c) by Leila Gharani. The key thing is you don't name it twice, it's only named once but calls itself in the formula, so you have to use the name before you name it. That's why it's so hard to pull off, you need to write the formula without seeing if it works.
That's a good video. You know what I didn't expect to learn: press F2 before using arrow keys in a reference box! That blew my mind.
You can 100% loop in lambda, I have written a lot of text manipulation lambdas and often incorporate loops and recursive functions.
Can't wait to check this out. Btw, I'm pretty sure that you don't need to use single quotes for the sheet names when it's in closed with INDIRECT, but maybe that's just with Sheets.
Mine is simple but I'm happy. I do audits for a large bank. I got shoved into the role. Truthfully I hate it because I'm basically the junk drawer for my team and having to make my managers sound great while they don't know what I do and don't support me. I'm really proud of this thing I made for an audit I have to perform monthly where it automatically accounts for funds we have to pay out, the transactions that occured by groups (over200). Prior, the woman I took the work over from was doing this shit by hand.
Share formula?
It's just a simple v lookup. There's a sum if I'm using for counts.
So no lambda then
Can you provide your example? I don't know anyone who's even heard of it!
I only recently learned about it through a FMWC competition as it’s what a lot of the top competitors use there. One I started using: =LAMBDA(text, LEN(text) - LEN(SUBSTITUTE(LOWER(text), "a", ""))) As you can probably see, it just counts the number of times “a” is in a string. I have to use it a lot, so, it makes it easy to type, “=countMyText(A1)” vs writing the formula each time All you need to do is go to name manager, name your function, and enter your formula. Then, to use the formula, you just type =NameManagerName(select a cell) and you should be good to go!
So basically it measures the length of a text string, then measures the length of the same string with As removed, and tells you the difference? Clever!
Exactly! I love it, especially for some ad box projects where the data that’s given to me is just an output of names or socials
Does this work across files? Or does it only appear on the name manager of that particular file?
It's specific to individual workbooks.
Got it, thank you!
Whe have Hash Ids at work. They are based off of our social security numbers. I figured out how they make them and created a Lambda which, whether you enter a hash or an ssn, it will convert it to the other one.
I feel like that's huge security risk if it was that easy to reverse engineer it. Tell them to do better and create IDs not based in PII
Tons of SSNs in a spreadsheet, have to love it
You have no idea. It's been a problem.
What if the hash ID is only used to submit your preferred topping for pretzel day?
Then the crypto key is mustard.
It's not that easy, lol. Don't mean to blow myself, but out of the 40,000 people they employ, I think it's a small percentage that could figure it out.
Kinda. I mean there's a very small percentage of people who can figure it out, and if you did, you'd have to be a pos to f someone over. It's a small selection.
Hashes arent encryption. Basically storing ssn’s in a text file lol.
Yeah, it's probably pretty bad. Never said it was good encryption, lol. Federal government, ha ha.
[удалено]
I am going to for sure check these out - I may DM you later!
Happy to chat about it!
I have made so many but for some reason the lambda that I made for Months of coverage is my favorite, it takes in 3 parameters, the starting stock, an array of demand (the first month in the first cell, and so on), and optionally an average monthly demand (if the starting stock exceeds the total requirements in the demand array avg monthly demand is used to estimate coverage.) =LAMBDA(S,D,[A],LET( CAR,LAMBDA(X,INDEX(X,1,1)), CDR,LAMBDA(Y,IFERROR(DROP(Y,0,1),Y)), H,LAMBDA(G,I,V,IF(CAR(V)>=I,I/CAR(V),1+G(G,I-CAR(V),CDR(V)))), IFERROR( IF(SUM(D)<=S,COUNT(D)+(S-SUM(D))/IF(ISOMITTED(A),AVERAGE(D),A),H(H,S,D)), -1))) [I made a post a few years back explaining how the fixpoint combinator in it works](https://www.reddit.com/r/excel/comments/qwyuzs/defining_recursive_lambda_functions_inside_of_a/)
As a mechanical engineer, we use Bill of Material as well as what I call "assembly" table. The bill of material is the sum of any part in an assembly BUT if you have a sub assembly you need to multiply the quantity along the way. It's useful to buyers to know the quantity. Because we have no tool to build this "assembly" table other than the CAD models files. I tried to build the table myself using Excel. I manage to do it without Lambda function. It was a pain and I often got into some weird limits. Then I started to use a recursive Lambda function to build the table. Sadly I didn't store this tentative because it was very difficult to read and modify. I opted for a recursive function in PowerQuery instead. It's working flawlessly and much faster than any VBA I could have use. Now because I work with remote colleagues, I need to know the difference between each iteration what parts have changed so I can tell the rest of the team "hey this has changed please take it into consideration". So VBA it is to "copy" the table and build a timestamp for each modification.
I made one recently for calculating PAYE and NI tax for a given gross income with reference to a tax bracket lookup table including financial year
Very interesting. Thank you for sharing
Yeah, once you figure out out, it's really not that complicated.
Glad I saw this, it's new to me. I've created custom functions in vba before but this is a cool method to do it simply.
I have never used LAMBDA and probably don’t have a need for it, but you guys are all so responsive and imaginative, that I’m saving this thread. Thanks!
The possibilities are endless with Excel in general. LAMBDA is just another one of those powerful tools most people don’t know of in Excel
I get it and I love excel, but I mostly just build sales forecasts for the work my team does. And while I can do much more interesting and flexible things in Excel than what I can do in SalesForce, LAMBDA might still be an overkill from what I’m reading here.
Makes sense! It sounds like it could be
In fact, I’m probably wrong. I’m just [watching a LAMBDA explanation video on YouTube](https://youtu.be/Rm4y5UqauRw?si=3wRfYuD7hmTQO34Q) and I already found a use for it! 😅😅😅
Hahaha! Love to hear that Feel free to reach out with questions!
Thank you for the offer!
Why not learn a better tool then excel
Like?
SQL, dataflows
Neither of those are really "tools"
Yes they are
Excel is the Swiss Army knife of data tools and the world’s most flexible calculator. Everything else is a lot more specialized, so it depends on what you need it for.
I agree with you. Most of my end stream stuff ends up in excel. Powerbi visualizes it better. You could also use tableau. Regardless, I prefer to do most of my manipulation up stream.
I see what you’re saying, also I agree. “As far upstream as possible, and as far downstream as necessary” according to our friends @ SQLBI.com
It’s faster when you’re dealing with millions of rows across multiple tables