Це відео не доступне.
Перепрошуємо.
FILTER Function with List of Partial Text Contains Criteria & Not Contains Criteria. EMT 1837
Вставка
- Опубліковано 12 сер 2024
- Download Excel File: excelisfun.net/files/EMT1835-...
Learn about how to use a list of partial text contains criteria to filter a list using the FILTER Function. See Contains and Does Not Contains Criteria. See three methods including the easiest version that uses the BYROWS function and LAMBDA Function rather than MMULT function and matrix algebra..
Topics:
1. (00:00) Introduction
2. (00:33) SEARCH Function
3. (01:20) TOROW Function
4. (20:20) 2023 formula
5. (02:33) 2020 or later formula
6. (02:46) 2010 or later formula
7. (02:53) ISNUMBER function and Double Negative
8. (03:09) BYROWS and LAMBDA Functions
9. (04:32) FILTER Function
10. (05:01) Filter by contains
11. (05:10) Filter by Not Contains using the NOT Function
12. (05:39) Summary
13. (05:59) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula
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
Great explanation , Thanks Mike !!!!!!!
You make it so easy to understand .... great video Mike!👍
Glad my stories can make complicated things lass complicated.
you are a true genius!!
Excellent. Mil thanks Mike.
As always..Great video tutorial amigo!!!!
You are one of the best of the best!!!!
Just a guy having fun with Excel ; )
Another great video Mike 👌
Glad you like it!!!!!
AMAZING ... this is perfect mike .... great example
Glad this helps!
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..... 😧
You are a master, thank you 👍
You are welcome!!!!!
Wow, so nice seeing Lambda and Torow. I still have to practice, but these were nice examples.... thanks mate!
You are welcome mate!!!
Thanks Mike for this EXCELlent video.
Thank you Fellow SMMS Teacher!!!!!
I like that SMMS@@excelisfun
Now is time for me to study lambda function :) another great vieo, thanks Mike
Here is my main LAMBDA video:
ua-cam.com/video/45v5NhPhopc/v-deo.html
@@excelisfun Super!!!! Thank you
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!!! : ) : )
Super Liked!
Token of Gratitude!
Thank you so much for the Super Thanks and your support, Ankursharma!!!!
Brilliant Mike! Thanks for another great video!
You are welcome!!!!
Thanks Mike!! That was great!!!! :) :)
Thanks, Awesome Member Teammate!!!!
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. : )
Wow! Awesome mike. Thanks a lot.
You are welome a lot!!!
Great! As usual, thanks a lot
You are welcomr a lot!
A brilliant video. I like using MMULT ! Depsite being an old function 😅
MMULT is matrix fun!!!
Great video!! Probably the best solution would be for MS to add an "enable wildcards" optional argument to FILTER!!
Niiiiiice!!!!!
Very nice sir thanks a lot .
You are welcome a lot!!!!
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.
Great video. Wait new topic
New topic next Sunday : )
Awesome!
Glad it is awesome for you!!!!
This formula tweak can be leveraged to filter on list of criteria or multiple criteria
Niiiiiice!!!
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?
Exactly🎉
Glad you like it!!!
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.
ua-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html
There's also a full course that goes from the basics to the advanced.
ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
@@brianxyz thanks for your help.....🙌
Super sir
Glad you like it!!!!
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
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.
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!
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 🙌🏿
Please make full tutorial on python in Excel
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)))
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.....🙌
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 !