WOW! This video came few hours after i searched youtube for such solution and didn't find what i was looking for, and then your video popped up in Home even though i'm subscribed .. i'm so lucky, THANK YOU SO MUCH!!
Mike this is brilliant as always. I did a more basic version based on adding individual Filters and combining them with + and came up with =FILTER(B6:D24,ISNUMBER(SEARCH(F6,C6:C24))+ISNUMBER(SEARCH(F7,C6:C24))+ISNUMBER(SEARCH(F8,C6:C24))) which works but your version more powerful so I'll now need to bite the bullet and try to learn the LAMBDA function as it looks uber powerful..... 😧
Thank you for the great filter video Mike. I was just struggling with a similar filter function at work, and was not able to figure it out. I going to try this. Then I have to go back and check out some of your videos on Lambda, because I still don't have a good understanding on that. Thanks Mike!
Oh Mike, this was a great trick but I still believe difficult for the average Excel user. Since I belong to this group I propose another solution: Just Merge the two tables, yes just merge, and then use the magic: "Use Fuzzy Logic" . At your first try you will NOT have a success, but guess what: set the "Threshold" to 0,1 !! Awesome.!! Greetings Bart....😉
Thanks, Bart, for solution for average users!!! I just never have trusted "fuzzy" becaue it is never 100%. But that may be a bias that I hold that may interfere with a "best" solution in some situations. : )
Nice formulas✌ Posted mine separately but YT did not show them.... So I will post it here also if you do not mind. Thank you!! =FILTER(B6:D24,REDUCE(1," "&F6:F8&" ",LAMBDA(v,i,v*ISERR(SEARCH(i," "&C6:C24&" "))))) This delivers this: 43526 ABCD 348 43527 ABCD XYsZ 387 43532 XYZAA 22 209 43533 GHI 319 43534 JKL 192 43537 OPQ JKL 291 43538 MNO 473 43539 PQRST WX 417 43540 WX 439 43541 YZ 346 has more 3 products than other formulas because ABCD contains ABC but actually is a different product. Same for XYZAA is a different prod even if it contains XYZ and AA Also BYROW iterates by the nr of rows, REDUCE method iterates by fixed nr. 3, (nr. products) no matter the rows nr.=> more efficient for large arrays
@@Excelambda Perfectly!!! 👏 I don't remember why I got it into my head that the accumulator could not be non-scalar... it works even with initial value... let say: =FILTER(B6:D24, REDUCE(C6:C24=C6:C24, F6:F8, LAMBDA(v, i, v * ISERR(FIND(i,C6:C24)) ))) kind of "game changer"... even in matrix algebra :)) ps. Really something strange happens in rendering your post. Did you reduce to some YT cipher? :))
@@viktorasgolubevas2386 Thanks!! accumulator can be anything, you can use also this: =REDUCE(B6:D24,F6:F8,LAMBDA(v,i,FILTER(v,ISERR(SEARCH(i,INDEX(v,,2)))))) or the accurate sol: =REDUCE(B6:D24," "&F6:F8&" ",LAMBDA(v,i,FILTER(v,ISERR(SEARCH(i," "&INDEX(v,,2)&" "))))) The coolest part of its behavior is when it's omitted , it will not take 0 or empty string "" value by default like any other argument, it will take the value of first iteration. Simple way to test =REDUCE(,{10,11},LAMBDA(v,i,v)) will return 10 and not 0 This is very powerful, used it to trigger different calculations of same formula depending on the value of first item. ✌ YT has glitches, a way to see my messages is to hit Sort by -> Newest first. Happens randomly from time to time to anyone.
With BYROW, I prefer using COUNTIF for this. I know you’re not a big fan of it, but to me it’s the most flexible method because it accepts wildcards and comparison operators. =FILTER(B6:D24, BYROW(C6:C24, LAMBDA(row, SUM(COUNTIF(row, F6:F8))))) Obviously for this to return results that contains the criteria, you would need to surround each one with asterisks. That’s what makes it flexible though… you can easily switch between contains, begins with, ends with, or does not contain, etc. simply by changing the wildcards. You can even switch to AND logic by swapping out SUM with PRODUCT, which works great on numbers and date columns. For example, you can search for records with an amount greater than or equal to 200 and less than 300. In cell F6 enter >=200 and in cell F7 enter
Greetings for your fabulous efforts. I have found your channel is way ahead than any other one Kudos to you..... ...i want to learn Power query with some m language knowledge please create a sequential playlist of your videos from scratch to pro level videos are there but not in sequential manner...it will be seriously helpful.
He's already got a play list of Power Query and M videos. ruclips.net/p/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK There's also a full course that goes from the basics to the advanced. ruclips.net/p/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1
Wonderful. For some reason BYROW doesn't detect your helper LAMBDA function. This is strange, it happens to me the same. Sorry, my English is not very good, I use Google Translator.
Hi Mike. let say a long string contain city name and we have a list of cities. I want formula to show me which city the list contain. Some time we need this trick in data cleaning.
A formula like this: =LOOKUP(2^15,SEARCH($H$7:$H$12,C7),$H$7:$H$12) $H$7:$H$12 = list of city names C7 is first cell in long string then copy formula down. To spill in 365: =BYROW(C7:C62,LAMBDA(r,LOOKUP(2^15,SEARCH($H$7:$H$12,r),$H$7:$H$12)))
Anyone know if using this to search cells with ascending values will it count a call twice. So if some values are 9 and some are 9.1, 9.2, 9.3 - I’ve spent the morning looking for a formula then remembered this channel which is great but now I’ve followed along I’m worried about duplicates.
You can not run an array operation on two columns with a different number of rows, but you can run an array operation on a set of columns (in a row) and set of rows (in a column) with different counts.
Would you kindly include in your video clearly to whicjh version of Excel are you referring to? There are much compatibility differences, so it is just not working in my Excel 2019.
Not Applicable to this video . However I am trying to find a formula that will deliver the following result . If I have a date which is between 1st and 11th of Jan, April July or October then the date reurned in my cell is 25th Jan , 25th April 25th July or 25th October AND if the date is any date from and including the 12th Jan, April July or October or any other month it returns the next relevant result which would be 25th Jan , 25th April 25th July or 25th October. For example Any date in Feb would return 25th April , Any date in May would return 25th July. or the 12th of April would return 25th July or the 12th of October would return 25th Jan for the next year . I have no idea how to solve this and Chat GPT has no idea either !
![Tee Grizzley - Detroit (Feat. 42 Dugg) [Official Video]](http://i.ytimg.com/vi/aeKfTtt0x14/mqdefault.jpg)
WOW! This video came few hours after i searched youtube for such solution and didn't find what i was looking for, and then your video popped up in Home even though i'm subscribed .. i'm so lucky, THANK YOU SO MUCH!!
Thank you 🙏 I have been looking for this solution for many days
You make it so easy to understand .... great video Mike!👍
Glad my stories can make complicated things lass complicated.
Mike this is brilliant as always. I did a more basic version based on adding individual Filters and combining them with + and came up with =FILTER(B6:D24,ISNUMBER(SEARCH(F6,C6:C24))+ISNUMBER(SEARCH(F7,C6:C24))+ISNUMBER(SEARCH(F8,C6:C24))) which works but your version more powerful so I'll now need to bite the bullet and try to learn the LAMBDA function as it looks uber powerful..... 😧
Thank you for the great filter video Mike. I was just struggling with a similar filter function at work, and was not able to figure it out. I going to try this. Then I have to go back and check out some of your videos on Lambda, because I still don't have a good understanding on that. Thanks Mike!
I hope this helped. If not, please tell me how your data and criteria set are set up.
Happy Teachers Day guruji( Sir) !!!! You are ocean of knowledge! lots of love from India!!!
Thank you for the Ocean Teacher wishes!!! : ) : )
you are a true genius!!
You are one of the best of the best!!!!
Just a guy having fun with Excel ; )
Wow, so nice seeing Lambda and Torow. I still have to practice, but these were nice examples.... thanks mate!
You are welcome mate!!!
Now is time for me to study lambda function :) another great vieo, thanks Mike
Here is my main LAMBDA video:
ruclips.net/video/45v5NhPhopc/видео.html
@@excelisfun Super!!!! Thank you
Great explanation , Thanks Mike !!!!!!!
Oh Mike, this was a great trick but I still believe difficult for the average Excel user. Since I belong to this group I propose another solution: Just Merge the two tables, yes just merge, and then use the magic: "Use Fuzzy Logic" . At your first try you will NOT have a success, but guess what: set the "Threshold" to 0,1 !! Awesome.!! Greetings Bart....😉
Thanks, Bart, for solution for average users!!! I just never have trusted "fuzzy" becaue it is never 100%. But that may be a bias that I hold that may interfere with a "best" solution in some situations. : )
AMAZING ... this is perfect mike .... great example
Glad this helps!
You are a master, thank you 👍
You are welcome!!!!!
Thanks Mike for this EXCELlent video.
Thank you Fellow SMMS Teacher!!!!!
I like that SMMS@@excelisfun
Another great video Mike 👌
Glad you like it!!!!!
As always..Great video tutorial amigo!!!!
Excellent. Mil thanks Mike.
Great video!! Probably the best solution would be for MS to add an "enable wildcards" optional argument to FILTER!!
Niiiiiice!!!!!
Great, Mike!
Back to School!
My triple homework :)
=FILTER(B6:D24,
BYROW(C6:C24,
LAMBDA(r,
1 = COLUMNS(TEXTSPLIT(r, F6:F8))
)))
=FILTER(B6:D24,
BYROW(C6:C24,
LAMBDA(r,
NOT(OR(ISNUMBER(FIND(F6:F8, r))))
)))
=FILTER(B6:D24,
BYROW(C6:C24,
LAMBDA(r,
0 = SUM(COUNTIF(r, "*"& F6:F8 &"*"))
)))
but I still love "old school" matrix algebra :))
Nice formulas✌ Posted mine separately but YT did not show them.... So I will post it here also if you do not mind. Thank you!!
=FILTER(B6:D24,REDUCE(1," "&F6:F8&" ",LAMBDA(v,i,v*ISERR(SEARCH(i," "&C6:C24&" ")))))
This delivers this:
43526 ABCD 348
43527 ABCD XYsZ 387
43532 XYZAA 22 209
43533 GHI 319
43534 JKL 192
43537 OPQ JKL 291
43538 MNO 473
43539 PQRST WX 417
43540 WX 439
43541 YZ 346
has more 3 products than other formulas because ABCD contains ABC but actually is a different product. Same for XYZAA is a different prod even if it contains XYZ and AA
Also BYROW iterates by the nr of rows, REDUCE method iterates by fixed nr. 3, (nr. products) no matter the rows nr.=> more efficient for large arrays
@@Excelambda
Perfectly!!! 👏
I don't remember why I got it into my head that the accumulator could not be non-scalar... it works even with initial value... let say:
=FILTER(B6:D24,
REDUCE(C6:C24=C6:C24, F6:F8,
LAMBDA(v, i,
v * ISERR(FIND(i,C6:C24))
)))
kind of "game changer"... even in matrix algebra :))
ps. Really something strange happens in rendering your post.
Did you reduce to some YT cipher? :))
@@viktorasgolubevas2386 Thanks!! accumulator can be anything, you can use also this:
=REDUCE(B6:D24,F6:F8,LAMBDA(v,i,FILTER(v,ISERR(SEARCH(i,INDEX(v,,2)))))) or the accurate sol:
=REDUCE(B6:D24," "&F6:F8&" ",LAMBDA(v,i,FILTER(v,ISERR(SEARCH(i," "&INDEX(v,,2)&" ")))))
The coolest part of its behavior is when it's omitted , it will not take 0 or empty string "" value by default like any other argument, it will take the value of first iteration.
Simple way to test
=REDUCE(,{10,11},LAMBDA(v,i,v)) will return 10 and not 0
This is very powerful, used it to trigger different calculations of same formula depending on the value of first item. ✌
YT has glitches, a way to see my messages is to hit Sort by -> Newest first. Happens randomly from time to time to anyone.
With BYROW, I prefer using COUNTIF for this. I know you’re not a big fan of it, but to me it’s the most flexible method because it accepts wildcards and comparison operators.
=FILTER(B6:D24, BYROW(C6:C24, LAMBDA(row, SUM(COUNTIF(row, F6:F8)))))
Obviously for this to return results that contains the criteria, you would need to surround each one with asterisks. That’s what makes it flexible though… you can easily switch between contains, begins with, ends with, or does not contain, etc. simply by changing the wildcards.
You can even switch to AND logic by swapping out SUM with PRODUCT, which works great on numbers and date columns. For example, you can search for records with an amount greater than or equal to 200 and less than 300. In cell F6 enter >=200 and in cell F7 enter
Thank you for the great formula, davidabuang!!!
You bet! Did you get the workbook I sent a few months ago regarding this topic?
Brilliant Mike! Thanks for another great video!
You are welcome!!!!
A brilliant video. I like using MMULT ! Depsite being an old function 😅
MMULT is matrix fun!!!
Super Liked!
Token of Gratitude!
Thank you so much for the Super Thanks and your support, Ankursharma!!!!
Thanks Mike!! That was great!!!! :) :)
Thanks, Awesome Member Teammate!!!!
Wow! Awesome mike. Thanks a lot.
You are welome a lot!!!
Great! As usual, thanks a lot
You are welcomr a lot!
Very nice sir thanks a lot .
You are welcome a lot!!!!
This formula tweak can be leveraged to filter on list of criteria or multiple criteria
Niiiiiice!!!
Great video. Wait new topic
New topic next Sunday : )
Greetings for your fabulous efforts.
I have found your channel is way ahead than any other one Kudos to you..... ...i want to learn Power query with some m language knowledge please create a sequential playlist of your videos from scratch to pro level videos are there but not in sequential manner...it will be seriously helpful.
He's already got a play list of Power Query and M videos.
ruclips.net/p/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK
There's also a full course that goes from the basics to the advanced.
ruclips.net/p/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1
@@brianxyz thanks for your help.....🙌
Awesome!
Glad it is awesome for you!!!!
Exactly🎉
Glad you like it!!!
Wonderful. For some reason BYROW doesn't detect your helper LAMBDA function. This is strange, it happens to me the same. Sorry, my English is not very good, I use Google Translator.
I am not sure why. Sorry.
AWESOME! what if i wanted my results to show only two non-contiguous columns? (for example, only show Date and Quantity)
Would this work for you: CHOOSECOLS([Mike's cool formula],1,3)
@thinktoomuchb4028 Thanks for the cool formula! Go Team!!!!!@@thinktoomuchb4028
Super sir
Glad you like it!!!!
Thank you for this !!!!!! I asked you this question when you did a similar thing with xmatch about a month ago 😂😂😂😂😂😂
Yes!!! It usually takes a while to make a post a video, but one month out: I glad it helps!!!!
@@excelisfun thank you very much I have an issue that keeps coming up at work and this will help solve that 🙌🏿
Hi Mike. let say a long string contain city name and we have a list of cities. I want formula to show me which city the list contain. Some time we need this trick in data cleaning.
A formula like this:
=LOOKUP(2^15,SEARCH($H$7:$H$12,C7),$H$7:$H$12)
$H$7:$H$12 = list of city names
C7 is first cell in long string
then copy formula down.
To spill in 365:
=BYROW(C7:C62,LAMBDA(r,LOOKUP(2^15,SEARCH($H$7:$H$12,r),$H$7:$H$12)))
Anyone know if using this to search cells with ascending values will it count a call twice. So if some values are 9 and some are 9.1, 9.2, 9.3 - I’ve spent the morning looking for a formula then remembered this channel which is great but now I’ve followed along I’m worried about duplicates.
Very cool! Could you use ISERROR instead of ISNUMBER with NOT?
ISERROR say TRUE for an error. ISNUMBER says TRUE for a number.
@@excelisfun So ISERROR would return TRUE when SEARCH doesn't find the text strings and you wouldn't need to use NOT. Very good. Thanks!
while using search formula why do you change the text from rows to columns? Is it necessary or can we get result without transposing it?
You can not run an array operation on two columns with a different number of rows, but you can run an array operation on a set of columns (in a row) and set of rows (in a column) with different counts.
@@excelisfun thanks for your reply.....🙌
Please make full tutorial on python in Excel
with the 'old' TRANSPOSE instead of TOROW it seems to work for me. Perhaps in one of the steps further on that might give an error?
Nope, that works just fine.
TOROW tends to be a little faster.
Would you kindly include in your video clearly to whicjh version of Excel are you referring to? There are much compatibility differences, so it is just not working in my Excel 2019.
I am using Microsoft 365 Excel. 2019 does not have these functions and features : (
😅 👌 👍
: ) : ) : ) : ) : ) : )
Can't we just use advance filter?
Yes you can : )
Not Applicable to this video . However I am trying to find a formula that will deliver the following result . If I have a date which is between 1st and 11th of Jan, April July or October then the date reurned in my cell is 25th Jan , 25th April 25th July or 25th October AND if the date is any date from and including the 12th Jan, April July or October or any other month it returns the next relevant result which would be 25th Jan , 25th April 25th July or 25th October. For example Any date in Feb would return 25th April , Any date in May would return 25th July. or the 12th of April would return 25th July or the 12th of October would return 25th Jan for the next year . I have no idea how to solve this and Chat GPT has no idea either !