Totally amazed by this!!! I didn't think that we could have lambda which handle all my circular reference with a map cases, and here Excel's BronJames comes with a solution 🙌🏽🔥 Can't wait to have your thought about the uno case🙌🏽🔥
Thank you, D. Incredible and daunting at the same time. Every time I think I have figured out a new technique, I am humbled by your mastery. Thank you for sharing these. I hope to apply them soon, especially the neighborOp and lambify.
Very interesting. Thnx for sharing. It looks for a large part similar to my setup. You may guess why... A couple of lambda's in my arsenal: Reverse. Works for both arrays and text Xmatch but return all occurrences Find but return all occurrence Xlookup on a 2d array Return destination for starting cell and a set of directions Curious what you would think of my full setup
Sounds like some great stuff! An array reverser is definitely on my list for when I next get some time to do development - it’s simple, but it would still save some time. And I’m definitely overdue to have some arrow lambdas…
Let's be honest, I always choke in the end... maybe someone else can take this and beat Andrew with it 😂 (Or more likely, he'll take it, use it better than me, and keep winning!)
Can you prepare this lambdas/ named ranges before the competition and use them in there? Like what is the process? Is it allowed? Thank you 💐 in advance.
Very similar to my collection, probably because I learned from just watching all your solves! Not sure I'm sold on the row column method yet using the thousands and the units since I seem to have some efficient methods with my collection. There's probably a lot of things that you can do with it that I just can't grasp yet so maybe one for the future!
Yeah, it’s hard to explain the value of that one without going into a lot of detail. Then again, what you have seems to be working quite well, so I wouldn’t worry about changing it now!
I kind of like the chaos of that, honestly… it lets people experiment with the functions right there in the file, and maybe they’ll come up with something interesting! (I do have an offline version in case something important gets deleted.)
If you want the most common value, you can use @_mostOf(array). More generally, @ for any calculated array will give you the first element of the array (it does more complicated things with a cell reference…).
Not sure what I'm feeling 🙂On the one hand "Awesome" (do hear this in Wyn Hopkins voice) or "Aaaaaaa" (how am I going to get familiar with this in a week). And a bit "grrrr" as I'm proud enough to want to have done this myself. So: thank you very much, much appreciated and good luck and have fun in Vegas (as I'm pretty sure you will end up there!)
On the ‘aaaaaa’ part, I would say just go from left to right. The stuff on maps and lambdify is pretty funky, and might take longer to get comfortable with how to use. The rest is all kinda obvious (things you’ve probably done before, even if it wasn’t in a LAMBDA), just packaged up to make them a bit quicker. Good luck! 🤞🏻
@@DimEarly this is me being the student and wanting the teacher to see what I've done (and maybe approve???) . And of course I took your _countOcc and tried myself if I could add the case sensitivity. This is what I came up with: _countOccSens = LAMBDA(str;sub;[sens];(LEN(str)-LEN(IF(sens;SUBSTITUTE(str;sub;);SUBSTITUTE(LOWER(str);LOWER(sub);))))/LEN(sub)) Sensitivity default when omitted is FALSE.
can I offer you a case sensitive Unique letter occurrences in a string - since unique disrespects case? =LAMBDA(letts,LET(ltrs,MID(letts,SEQUENCE(LEN(letts)),1), declen,LEN(SCAN(letts,ltrs,LAMBDA(a,v,SUBSTITUTE(a,v,)))), prev,DROP(VSTACK(LEN(letts),declen),-1), occrs,prev-declen, SORT(FILTER(HSTACK(ltrs,occrs),occrs>0),{2,1},-1)))(B2)
I love it! Really creative approach! I was hoping you might be able to just do SCAN(letts,ltrs,SUBSTITUTE), since you’re giving the first two arguments in the right order and omitting the third anyway, but no such luck…
Thanks so much for this @DimEarly! I'm slowly making my way through the video and improving my own lambda file. Many of these I already had a simpler version. I'm only 20 minutes in right now and wondering about an issue I'm having. I have a _strToArray lambda (basically your _ltrs) and a _mostOf lambda that both work wonderfully. But, when I try to do =_mostOf(_strToArray("asdpogihlkjfpoweij")) This causes an error. Any idea what is wrong? _strToArray: =LAMBDA(str,[size],[allSeq], LET( sz,IF(ISOMITTED(size),1,size), allS,IF(allSeq,1,sz), starts,SEQUENCE(INT(LEN(str)/allS)-IF(allSeq,sz-1,0),,1,allS), ans,MID(str,starts,sz),ans)) _mostOf: =LAMBDA(array,[list],[insensitive], LET(options,_unique(TOCOL(array),NOT(insensitive)), counts,COUNTIF(array,options), mx,MAX(counts), best,XLOOKUP(mx,counts,options), ans,HSTACK(best,mx), IF(list,SORT(HSTACK(options,counts),2,-1),ans))) _unique: Allows me to make unique case-sensitive. =LAMBDA(array,[sensitive],IF(sensitive,REDUCE(,array,LAMBDA(a,v,IF(SUM(--EXACT(a,v)),a,VSTACK(a,v)))),UNIQUE(array))) If I put =_strToArray(string) in A1, then =_mostOf(A1#) elsewhere, it works as intended. So confused. Thanks again!
Yup, I can tell you what’s wrong there - and it’s one of the hardest issues to debug if you haven’t hit it before! The problem is the COUNTIF. For some reason, the range inputs to COUNTIF (and the other members of the *IF[S] family) have to be a range - it errors out if you give it a calculated array. I’m really hoping they fix it at some point! That’s why my version uses the odd-looking SUM(-(A=B)) syntax - if you use my mostOf with your strToArray, it should work.
@@DimEarly Thanks for the help. I remember seeing this in a previous video of yours, need to remember it! The one problem with this fix, is that I was attempting to make an option where it would count them in a case-sensitive way (if requested). My original lambdas did this, but yours does not. Is there any way to make that sum(--(a-b)) be case-sensitive? If I knew the strings were only a single character, I could do it with code. But what if I want it to work on longer strings? {ABC; aBc; abC; AbC; abC} should result in abC: 2. Instead, it just returns {ABC: 5, aBc: 5, abC: 5, AbC: 5} Any thoughts on this?
@@nemoyatpeace The sum instead of countif should be OK for that - you could replace (a=b) with EXACT(a,b), which is the case sensitive equivalent. But getting a unique list in a way that’s case sensitive is the harder part. I guess you could use MID to split each element to letters, UNICODE to convert to codes, and then BYROW / ARRAYTOTEXT to recombine each one - so you’d get something like 65, 66, 67 for ABC, but 97, 98, 67 for abC. Pretty tricky stuff!
@@nemoyatpeace Sorry, I just realized your unique already does that part (quite nicely, too!). But your mostOf isn’t case sensitive even without the COUNTIF array issue, is it? I think COUNTIF is case insensitive in the way it matches.
@@DimEarly Perfect, thanks. I already have the case-sensitive unique (included it in my original comment). I just created this for the version that counts specifically characters in a string, but the Exact function is much cleaner! LAMBDA(string,[order],[list],[insensitive], LET(str,_strToArray(string), array,IF(insensitive,str,CODE(str)), result,_mostOf(array,order,list,insensitive), ans,IF(insensitive,result,HSTACK(CHAR(TAKE(result,,1)),CHOOSECOLS(result,2))), ans))
Not sure how I ended up on this video, and I don't understand any of it, but it looks like you've made some people very happy.
This might be my favorite comment of the day! 😂
Totally amazed by this!!!
I didn't think that we could have lambda which handle all my circular reference with a map cases, and here Excel's BronJames comes with a solution 🙌🏽🔥
Can't wait to have your thought about the uno case🙌🏽🔥
Thanks Christian! (I haven’t had a chance to try the case yet, but I’m looking forward to it!)
You have my total respect for this.
Thank you, D. Incredible and daunting at the same time. Every time I think I have figured out a new technique, I am humbled by your mastery.
Thank you for sharing these. I hope to apply them soon, especially the neighborOp and lambify.
Thanks Michael! There's always more to learn : )
(By the way, those two are my favorites too!)
Thanks for sharing this, very impressive!
Perfect video to start my weekend!
Have not viewed yet but you are a brave man
Haha - keeping secrets just doesn't sit well with me : )
Great talent. Thanks for sharing!!
Very interesting. Thnx for sharing. It looks for a large part similar to my setup. You may guess why...
A couple of lambda's in my arsenal:
Reverse. Works for both arrays and text
Xmatch but return all occurrences
Find but return all occurrence
Xlookup on a 2d array
Return destination for starting cell and a set of directions
Curious what you would think of my full setup
Sounds like some great stuff! An array reverser is definitely on my list for when I next get some time to do development - it’s simple, but it would still save some time. And I’m definitely overdue to have some arrow lambdas…
Could you share your lambda's here?
Brave video to release in October
Let's be honest, I always choke in the end... maybe someone else can take this and beat Andrew with it 😂 (Or more likely, he'll take it, use it better than me, and keep winning!)
@@DimEarly The die is not yet cast!
Can you prepare this lambdas/ named ranges before the competition and use them in there? Like what is the process? Is it allowed?
Thank you 💐 in advance.
Subscribed and now your fan
Very similar to my collection, probably because I learned from just watching all your solves! Not sure I'm sold on the row column method yet using the thousands and the units since I seem to have some efficient methods with my collection. There's probably a lot of things that you can do with it that I just can't grasp yet so maybe one for the future!
Yeah, it’s hard to explain the value of that one without going into a lot of detail. Then again, what you have seems to be working quite well, so I wouldn’t worry about changing it now!
Could you share your lambda's here?
@@sledgehammer-productions I will share my collection at some point
wow very nice!
impressive as usual, sharing your knowledge is really inspiring, could you please lock the files, as it seems users can edit the files.
I kind of like the chaos of that, honestly… it lets people experiment with the functions right there in the file, and maybe they’ll come up with something interesting!
(I do have an offline version in case something important gets deleted.)
Thank you!!!
What if ‘_mostOf’ returns the most values?
I used Index with row 1 and column 1 to get it. maybe you know how to modify it
thanks for your help!
If you want the most common value, you can use @_mostOf(array). More generally, @ for any calculated array will give you the first element of the array (it does more complicated things with a cell reference…).
Not sure what I'm feeling 🙂On the one hand "Awesome" (do hear this in Wyn Hopkins voice) or "Aaaaaaa" (how am I going to get familiar with this in a week). And a bit "grrrr" as I'm proud enough to want to have done this myself. So: thank you very much, much appreciated and good luck and have fun in Vegas (as I'm pretty sure you will end up there!)
On the ‘aaaaaa’ part, I would say just go from left to right. The stuff on maps and lambdify is pretty funky, and might take longer to get comfortable with how to use. The rest is all kinda obvious (things you’ve probably done before, even if it wasn’t in a LAMBDA), just packaged up to make them a bit quicker.
Good luck! 🤞🏻
@@DimEarly this is me being the student and wanting the teacher to see what I've done (and maybe approve???) . And of course I took your _countOcc and tried myself if I could add the case sensitivity. This is what I came up with:
_countOccSens = LAMBDA(str;sub;[sens];(LEN(str)-LEN(IF(sens;SUBSTITUTE(str;sub;);SUBSTITUTE(LOWER(str);LOWER(sub);))))/LEN(sub))
Sensitivity default when omitted is FALSE.
@@sledgehammer-productions Just use EXACT maybe?
Many thanks for listening my request I am very greatful
WOW WOW WOW
NICE
Hello friend🎉😊
👋🏻
can I offer you a case sensitive Unique letter occurrences in a string - since unique disrespects case?
=LAMBDA(letts,LET(ltrs,MID(letts,SEQUENCE(LEN(letts)),1),
declen,LEN(SCAN(letts,ltrs,LAMBDA(a,v,SUBSTITUTE(a,v,)))),
prev,DROP(VSTACK(LEN(letts),declen),-1),
occrs,prev-declen,
SORT(FILTER(HSTACK(ltrs,occrs),occrs>0),{2,1},-1)))(B2)
I love it! Really creative approach!
I was hoping you might be able to just do SCAN(letts,ltrs,SUBSTITUTE), since you’re giving the first two arguments in the right order and omitting the third anyway, but no such luck…
Thanks so much for this @DimEarly! I'm slowly making my way through the video and improving my own lambda file. Many of these I already had a simpler version. I'm only 20 minutes in right now and wondering about an issue I'm having.
I have a _strToArray lambda (basically your _ltrs) and a _mostOf lambda that both work wonderfully. But, when I try to do
=_mostOf(_strToArray("asdpogihlkjfpoweij"))
This causes an error.
Any idea what is wrong?
_strToArray: =LAMBDA(str,[size],[allSeq],
LET(
sz,IF(ISOMITTED(size),1,size),
allS,IF(allSeq,1,sz),
starts,SEQUENCE(INT(LEN(str)/allS)-IF(allSeq,sz-1,0),,1,allS),
ans,MID(str,starts,sz),ans))
_mostOf: =LAMBDA(array,[list],[insensitive],
LET(options,_unique(TOCOL(array),NOT(insensitive)),
counts,COUNTIF(array,options),
mx,MAX(counts),
best,XLOOKUP(mx,counts,options),
ans,HSTACK(best,mx),
IF(list,SORT(HSTACK(options,counts),2,-1),ans)))
_unique: Allows me to make unique case-sensitive.
=LAMBDA(array,[sensitive],IF(sensitive,REDUCE(,array,LAMBDA(a,v,IF(SUM(--EXACT(a,v)),a,VSTACK(a,v)))),UNIQUE(array)))
If I put =_strToArray(string) in A1, then =_mostOf(A1#) elsewhere, it works as intended. So confused.
Thanks again!
Yup, I can tell you what’s wrong there - and it’s one of the hardest issues to debug if you haven’t hit it before!
The problem is the COUNTIF. For some reason, the range inputs to COUNTIF (and the other members of the *IF[S] family) have to be a range - it errors out if you give it a calculated array. I’m really hoping they fix it at some point!
That’s why my version uses the odd-looking SUM(-(A=B)) syntax - if you use my mostOf with your strToArray, it should work.
@@DimEarly Thanks for the help. I remember seeing this in a previous video of yours, need to remember it!
The one problem with this fix, is that I was attempting to make an option where it would count them in a case-sensitive way (if requested). My original lambdas did this, but yours does not. Is there any way to make that sum(--(a-b)) be case-sensitive? If I knew the strings were only a single character, I could do it with code. But what if I want it to work on longer strings? {ABC; aBc; abC; AbC; abC} should result in abC: 2. Instead, it just returns {ABC: 5, aBc: 5, abC: 5, AbC: 5}
Any thoughts on this?
@@nemoyatpeace The sum instead of countif should be OK for that - you could replace (a=b) with EXACT(a,b), which is the case sensitive equivalent. But getting a unique list in a way that’s case sensitive is the harder part. I guess you could use MID to split each element to letters, UNICODE to convert to codes, and then BYROW / ARRAYTOTEXT to recombine each one - so you’d get something like 65, 66, 67 for ABC, but 97, 98, 67 for abC. Pretty tricky stuff!
@@nemoyatpeace Sorry, I just realized your unique already does that part (quite nicely, too!). But your mostOf isn’t case sensitive even without the COUNTIF array issue, is it? I think COUNTIF is case insensitive in the way it matches.
@@DimEarly Perfect, thanks. I already have the case-sensitive unique (included it in my original comment).
I just created this for the version that counts specifically characters in a string, but the Exact function is much cleaner!
LAMBDA(string,[order],[list],[insensitive],
LET(str,_strToArray(string),
array,IF(insensitive,str,CODE(str)),
result,_mostOf(array,order,list,insensitive),
ans,IF(insensitive,result,HSTACK(CHAR(TAKE(result,,1)),CHOOSECOLS(result,2))),
ans))