Excel Magic Trick 1382: Extract Records With Multiple Contains (Partial Text) Criteria: 4 Examples

No video

Excel Magic Trick 1382: Extract Records With Multiple Contains (Partial Text) Criteria: 4 Examples

Рет қаралды 46,371

Күн бұрын

Download Files:
Start File: excelisfun.net...
Finish File: excelisfun.net...
See how to:
1. (00:15) Introduction to Contains Criteria (Partial Text Criteria) in an OR Logical Test, when search for records with Multiple Contains Criteria.
2. (02:00) #1 Filter & Helper Column with Formula that uses OR, ISNUMBER & SEARCH functions. This Array Formula requires Ctrl + Shift + Enter.
3. (07:23) #2 Filter & Helper Column with Formula that uses ISNUMBER, LOOKUP & SEARCH functions. This Array Formula does NOT require Ctrl + Shift + Enter.
4. (12:11) #3 Easiest solution: Advanced Filter with Formula Criteria, where formula uses LOOKUP & SEARCH functions. This Array Formula does NOT require Ctrl + Shift + Enter.
5. (15:56) #4: Array formula #1 to count matching records using an OR Logical Test with Multiple Contains Criteria. Use SEARCH function with two different size Function Argument Array Operations that require that we flip one of the ranges using the TRANSPOSE Array Function. Also see the functions: ISNUMBER, MMULT, ROW and SUM. This Array Formula requires Ctrl + Shift + Enter.
6. (21:45) Introduction to Matrix Multiplication, the Excel MMULT Array Function and detailed explanation of the mechanics of how it works in Array Formula to add all the numbers for each row in a two-way array and produce a single column of totals. This Array Function requires Ctrl + Shift + Enter.
7. (30:18) #4: Array formula #2 to extract records. See the functions: SEARCH, TRANSPOSE, ISNUMBER, MMULT, ROW, IF, ROWS, INDEX and SMALL. This Array Formula requires Ctrl + Shift + Enter.
8. (36:52) Test Array Formulas when source data changes.
9. (37:39) Summary.
Match Job Title to List of Key Words
Reference Video: Excel Magic Trick 1383: Conditional Format Row w OR Logical Test with Multiple Partial Text Criteria

Пікірлер: 80

@LeilaGharani 7 жыл бұрын

I also really like the advanced filter method with the formula as criteria. Really clean! The array formula is great too. Great explanation. Thank you.

@excelisfun 7 жыл бұрын

You are welcome! As you well know with your great Array Formula Videos, it is amazing what you can do with Excel formulas : ) ... Yes, no doubt, Advanced Filter Formula Criteria is really efficient!

@dougmphilly Жыл бұрын

@@excelisfun this is like listening to sandy koufax & mickey mantle of excel self help videos.

@excelisfun Жыл бұрын

@@dougmphilly LOVE the baseball simile!!!!! Sandy and Mickey sure was fun to watch : )

@vladimirgerasimenko7597 7 жыл бұрын

That is the best extraction I have ever seen. Thanks.

@excelisfun 7 жыл бұрын

You are welcome!

@MWRent 5 жыл бұрын

Brilliant! The first 10 mins was all I needed!

@excelisfun 5 жыл бұрын

Glad it helps, m w!!!!! When you need help, excelisfun is here to guide and have fun! Thanks for your support with your comment, thumbs up and of course your Sub : )

@farishaaulina 3 жыл бұрын

Thank you so much for this detailed video! You saved my 2020 life!

@johnmatta9577 7 жыл бұрын

Oh my heaven...why are you doing this to us Mike😊..unbelievable formula..I guess that would be the most complicated formula you've done; isn't it?

@omarioxibit 7 жыл бұрын

John Matta . I

@excelisfun 7 жыл бұрын

Not even close to the most complicated. But it does involve Matrix Multiplication so it is complex.

@MoskaTalks 7 жыл бұрын

Could not be more timely, this solves a problem we were trying to figure out in the office.

@excelisfun 7 жыл бұрын

Awesome! Timing & Excel lead to accomplishments : )

@nadermounir8228 3 жыл бұрын

Super amazing video using the mmult function. Mike U made me love array formuals !!

@excelisfun 3 жыл бұрын

Glad you love the array formulas : ) : )

@michaeldolan7507 3 жыл бұрын

Mike thank you! Just discovered this and it has really helped me with a problem I was trying to solve. Keep up the good work

@excelisfun3903 3 жыл бұрын

Glad to help MICHAEL!!!

@ExcelVbaIsFun 7 жыл бұрын

Good stuff! Thanks Mike for the amazing descriptions and keyboard shortcuts. You da man!

@excelisfun 7 жыл бұрын

No, you da man!!!! I don't see you around KZfaq anymore, but I think I heard you are a star at some other site, right? Like Udemy or something? where are you making videos and teaching these days?

@sesagolu 7 жыл бұрын

Thank you. I found the link. I love all of your videos. I've learned so much.

@excelisfun 7 жыл бұрын

You are welcome! Thank you for clicking the Thumbs Up!!!

@isogulaleb 6 жыл бұрын

That's crazy! Exactly what I was looking for! Works like a charm! Thanks a million! :)

@excelisfun 6 жыл бұрын

Crazy Excel fun, I hope : ) You are welcome a million! Thanks for the support with your comment, Thumbs Up and Sub : )

@isogulaleb 6 жыл бұрын

One small hitch though: no way to arrange the results? The order of the rows cannot be changed I suppose.

@danwest6267 7 жыл бұрын

Sweet!! I was looking for a payee lookup report formula. Excellentee!

@user-ot8wc9id2s 5 жыл бұрын

Love your video!!!!!!

@excelisfun 5 жыл бұрын

Glad you love the video!!!! Thanks for your support with your comment, thumbs up and your Sub : )

@1oakdesign353 2 жыл бұрын

Awesome job! Can Excel auto populate if you set up a search page and have data pages with multiple fields. Thank you and feel free to ask for more information if needed. I know this is very minimal information to give an answer.

@louisreadus4519 6 жыл бұрын

I'm working on a workbook with multiple sheets and they're all linked to one master sheet with macros for filtering. It takes extremely long for the mastersheet to filter. Before I linked the sheets the master sheet worked fine because I manually enter data that were in the other sheets. The problem with manually imputing the data is that it's double work. I would love to email you my workbook so you can advise me on better options.

@mohamedchakroun4973 7 жыл бұрын

Waw Mike absolutely great it is unbelivebale what you are doing

@excelisfun 7 жыл бұрын

Glad you like it : )

@Will_Moffett 4 жыл бұрын

Great vid, induced me to buy an Excel is Fun mug in appreciation. In my experience the INDIRECT function doesn't play nice inside the isnumber(search( construct, just an FYI.

@excelisfun 4 жыл бұрын

Thank you for your support, Will : )

@J0n0nI 7 жыл бұрын

amazing lesson and great effort thank you so much

@excelisfun 7 жыл бұрын

You are welcome so much!!

@naresh1010ful 2 жыл бұрын

third method of array formula is a complete bouncer. not sure what's going on with MMult function

@eCabinetstipsandtricks 7 жыл бұрын

Mike, Thanks. for the video. It was very helpful

@excelisfun 7 жыл бұрын

You are welcome. Glad it is helpful : )

@johnborg6005 5 жыл бұрын

Great!!!! Simply GREAT : )

@excelisfun 5 жыл бұрын

Thanks John : ) : ) : )

@AmexL 5 жыл бұрын

What if the Job Title column contained cells where there’s multiple titles, or for example, contained two or more of the lookup values such as ‘BI’ and ‘DAX’ and you want your lookup result to include both BI and DAX in the same result cell?

@sharkseye9 6 жыл бұрын

i have an issue...this would seem to work but my issue is the cell field data has numbers, text, double spaces, dates, commas, and sometimes forward slashes...I need to extract names of vendors from that data that is not lined up and sometimes the vendor name is repeated. I think this may be a job for Superman!

@millawitch 3 жыл бұрын

Awesome - I've been trying to make the SEARCH function work with the Office 365 FILTER function - did a test using this MMULT formula technique in the criteria argument and it seems to do the job! Maybe I'm missing something basic, can anyone think of a better solution? Thanks

@robinmarks6351 7 жыл бұрын

Great Video. but I've got a question that pushes it a bit further. I'm trying to do the same thing you've just done in the video but instead of returning a TRUE or FALSE statement in the "contains?" column, I'd like it to return which one it found. My list is a bit easier as each equivalent of "Job Title" contains exactly one word from the list. Could you help?

@imsaurabhgupta 4 жыл бұрын

Not able to download Excel - Please share Link of this Excel "Excel Magic Trick 1382: Extract Records With Multiple Contains (Partial Text) Criteria: 4 Examples"

@johnborg5419 6 жыл бұрын

Beautiful !!!!

@excelisfun 6 жыл бұрын

Yes!!!!!!!

@farhanahmed2042 5 жыл бұрын

G8 work , what if i want the matching records in columns rather than row wise

@philmanfredi6119 7 жыл бұрын

Awesome - as ever...

@excelisfun 7 жыл бұрын

Glad you liked it : )

@MySpreadsheetLab 7 жыл бұрын

My thoughts exactly! Thanks Mike 😎

@excelisfun 7 жыл бұрын

Thanks, Kevin : )

@PrincePedia 7 жыл бұрын

Absolutely Great , Thanks Mike : )

@excelisfun 7 жыл бұрын

Glad you like it : )

@mitzok 6 жыл бұрын

Hi Mike, very helpful video but for some reason I can't make it work properly. I opted for the #3 Solution, but I get wrong answers ... it works in the sense that the LOOKUP formula spits out the correct values but when I apply the AF it copies also wrong stuff. I search for "URB" texts but I also get results with other texts and also missing some "URB" ones. Any insight ? From what I tested .. the second result it's ignored and the first wrong one is showed instead.

@masterof 7 жыл бұрын

Thanks Mike. I have a strange chanllenge: I need to merge the Column A ("Part number") into one list, from 3 different tables. Each table have a few other columns, one of the column is "type", another is "status". To extract the data from a table which meet 2 conditions (For example, "type" to be "ABC", and "status" is not "End of Sale" (can be any other text but "End of Sale"). In the INDEX command, to get the ROW number that fits the conditions, I tried to use "AND" to two arrays, but AND can only delivery one result, rather than an array of results. I tried to use * to join the two arrays, but couldn't work into ROW(). Is there another formula function can be used?

@ia7meDi 7 жыл бұрын

Thanks mike.. I wonder if I can join the challenges you and mr excel do? it seems i could have some fun too

@excelisfun 7 жыл бұрын

Mr Excel and I have not done Duels in a few years. Do you make Excel videos?

@jegane 7 жыл бұрын

hi mike, @7:03 what if we only want to conditionally format cells? can't we use array formula inside conditional formatting? (don't want to extract, just highlight)(@13:53)

@excelisfun 7 жыл бұрын

Same formula as Advanced Filter, but to format whole row formula would be with a Mixed Cell Reference: =LOOKUP(2^15,SEARCH($K$11:$K$17,$F11)) AND MAKING sure that the Active Cell when highlighting table is in the first row.

@rebeexpo 7 жыл бұрын

Hey Mike , for the magick tricks where would you advise to start ? number 1 ?

@excelisfun 7 жыл бұрын

No, the Magic Tricks are random topics. I have two classes that I teach at Highline College that cover almost all topics in Excel in logical order. #1 Excel Basics: kzfaq.info/sun/PL3FBEE51974F03CCF #2: Comprehensive Excel Class: kzfaq.info/sun/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw I have other class too, like Statistics...

@jeanyveslaval4293 7 жыл бұрын

thank you for this video Mike, would it be effective over a dataset of 80000rows

@excelisfun 7 жыл бұрын

You are welcome. The Advanced Filter Method is THE best way to go for a data set that big.

@alexj8117 7 жыл бұрын

How about a Power Query technique for this?

@excelisfun 7 жыл бұрын

It is much more complicated. Unless you have data more than a million rows, Advanced Filter is the way to go. Bill Szysz has two great methods, hopefully he will make a video : )

@sesagolu 7 жыл бұрын

I can't find the link for Magic Trick 1382. Is it the People.highline.edu file? I looked at that but couldn't find anything. Please help.

@excelisfun 7 жыл бұрын

Click link below video, then use Find Feature Ctrl + F, and find "1382", this will jump you right to the file. Thank you for the Thumbs Up.

@planxlsm 5 жыл бұрын

8:55 11:09 13:54 - Filter 17:52 - SEARCH ( TRANSPOSE 21:24 - {1, 0, 0; 0, 0, 0 ;...] MMULT

@excelisfun 5 жыл бұрын

So, finally, after all your posts... now I understand what you are doing: you are leaving a time map for the topics you might lookup later? Is that correct, PLAN XLSM?

@excelisfun 5 жыл бұрын

This is another good one with good comparisons... : )

@planxlsm 5 жыл бұрын

@@excelisfun yes, Mike !

@mwachambigeorge7728 7 жыл бұрын

For a person with the most basic knowledge of excel, where do I start from?

@excelisfun 7 жыл бұрын

Here is my starter Excel class: kzfaq.info/sun/PL3FBEE51974F03CCF

@mwachambigeorge7728 7 жыл бұрын

ExcelIsFun thanks

@BillSzysz1 7 жыл бұрын

Awsome!! AF rules :-)))

@excelisfun 7 жыл бұрын

I sooooooo agree: Advanced Filter Rules : )

@excelisfun 7 жыл бұрын

Posted above: PQ is much more complicated. Unless you have data more than a million rows, Advanced Filter is the way to go. Bill Szysz has two great methods, hopefully he will make a video : )