Dude! All do respect...lol. You saved my hide. Been trying to figure out how to do this with multiple criteria into one cell for about two weeks now. I was not able to accomplish this through any other video. My friend this is amazing. Can't thank you enough. Pass the collection plate around for this one...lol.
@ExcelCampus
7 күн бұрын
Glad it helped. 😀
@PRHC38
5 күн бұрын
@@ExcelCampus absolutely! One thing however. If possible please add how to format using the “unique” function. I was able to create the formula based on what you have here but then I saw that I had duplicates. Then I remembered to use “unique” in order to correct that. Other than that this tutorial is perfect! Thanks again.
@ExcelCampus
4 күн бұрын
Great suggestion, Toure! For anyone else reading this, you can wrap FILTER in UNIQUE to remove duplicates on the results of FILTER. =UNIQUE(FILTER()) I'm planning to do a follow-up video on FILTER based on all the great questions and suggestions in the comments here. I'll include this example. Thanks again and have a nice day! 🙂
@IvanCortinas_ES
12 сағат бұрын
Excellent tutorial, as always, Jon. Very interesting example using ARRAYTOTEXT combined with FILTER. Thanks for sharing.
@ExcelCampus
47 минут бұрын
You're welcome! 😀
@bbotzong
10 күн бұрын
Nice tutorial, Jon. Thanks for sharing!!!!!
@ExcelCampus
10 күн бұрын
Thanks Bill! 🙌
@warrenanderson412
7 күн бұрын
As always...great content!
@chrism9037
10 күн бұрын
Nice video and very well-explained, thanks Jon!
@ExcelCampus
10 күн бұрын
Thanks Chris! 🙌
@chazhinkeldey2362
10 күн бұрын
Wow! Thanks for this! I literally needed it last night and walked in this morning and had this video in my inbox!!
@ExcelCampus
10 күн бұрын
Amazing! Thanks for sharing Chaz. I'm happy to hear it helped. 🙏
@rrki
10 күн бұрын
Great material. Thanks John
@yyusof78
9 күн бұрын
Marvelous mr Jon
@bennyjulong2033
10 күн бұрын
Awesome ! It's very useful tip to me for my daily routine work Thanks a lot ❤
@ExcelCampus
10 күн бұрын
I'm happy to hear it. Thanks Benny! 🙌
@potatocraftd6629
8 күн бұрын
I use this filter function for dynamic live reports
@virginia555
10 күн бұрын
Super useful, thank you
@ExcelCampus
10 күн бұрын
Thanks Daniel! 🙂
@heatherr5191
10 күн бұрын
GREAT examples, and very clear explanation - thanks as always Jon. This is going into my current set of review files to help me remember this new way to do things.
@ExcelCampus
10 күн бұрын
Thanks Heather! I'm happy to hear you'll be adopting these techniques. 😊
@michael6malone
9 күн бұрын
Thank you Jon. That was very well explained. I feel like an expert :)
@angelaisland4036
10 күн бұрын
Great information as always! Thanks Jon!
@ExcelCampus
10 күн бұрын
Thanks Angela! 🙂
@35jays
10 күн бұрын
Thanks!!
@OneToothedWonder
9 күн бұрын
Great tutorial. TYSM.
@tomjun8945
10 күн бұрын
Very helpful video! Thank you for your content.
@ExcelCampus
9 күн бұрын
You're welcome! 😀
@kellershouse
10 күн бұрын
When you are using the ArrayToText could you use an ALT ENTER to delimit the results to the same cell but with a formatted wrap text. So the cells would expand with multiple values?
@ExcelCampus
10 күн бұрын
Great question, Kent! Yes, there are few ways to go about this. You can use the CHAR(10) function as a delimiter for TEXTJOIN. Use TEXTJOIN instead of ARRAYTOTEXT so we can specify the delimiter. The 10 in CHAR(10) returns a line break as the delimiter between each value. The formula looks like the following. =TEXTJOIN(CHAR(10),TRUE,FILTER(tblContacts6[[Contact Name]:[Phone Number]],tblContacts6[Customer Name]=B4)) You must apply Wrap Text formatting to the cell and increase the row height to see the results on multiple lines within the cell. Since the array for FILTER contains two columns, the results will look like the following. Ketty 5552264675 Justine 5552879911 Inger 5553794109 Jimmy 5554385379 If you want to get both name and phone number on the same line, then you can use BYROW with LAMBDA to first join each row with a delimiter, then join the rows with a line break. Here is what that formula looks like. =TEXTJOIN(CHAR(10),TRUE,BYROW(FILTER(tblContacts6[[Contact Name]:[Phone Number]],tblContacts6[Customer Name]=B4),LAMBDA(row,TEXTJOIN(", ",TRUE,row)))) And the results will look like the following. Ketty, 5552264675 Justine, 5552879911 Inger, 5553794109 Jimmy, 5554385379 I hope that helps. I'll do a follow up video on these techniques. I think they are great ones to know for certain scenarios.
@richardhay645
10 күн бұрын
One problem with CHOOSECOLS/ROWS is that they don't accept cell references. If you add or delete rcolums/rows the formulas break. Also clicking on a column name is more convenient than counting columns in a many-column data set
@ExcelCampus
10 күн бұрын
Great point, Richard! We can use the XMATCH function within CHOOSECOLS to help prevent this issue. The formula would look like the following. =FILTER(CHOOSECOLS(tblContacts3[[Contact Name]:[State]],XMATCH(C3:E3,tblContacts3[[#Headers],[Contact Name]:[State]])),tblContacts3[Customer Name]=B4) XMATCH will look up all three header values in C3:H3 in the table that contains the source data, and return an array of column numbers {1,2,4} to CHOOSECOLS. This allows for columns to be inserted/deleted and also more flexibility with what is displayed in the results. I'll do a follow up video on more advanced techniques and include this one. Thanks again and have a nice day! 🙂
@richardhay645
10 күн бұрын
@ExcelCampus yes!! I'm down for a video on as many advanced topics with FILTER as you can come up with. I'm also down with Microsoft modifying FILTER to accept cell references. Finally I'm down with trying CoPilot to see if it can duplicate your EXMATCH construction to save me a bunch of keystroks!! Thanks for your help.
@viktorasgolubevas2386
7 күн бұрын
@@ExcelCampus Great, Jon! Actually, in most cases of "CHOOSECOLS/XMATCH" we could reference just a table instead of a structured diapason =FILTER(CHOOSECOLS(tblContacts3, XMATCH(C3:E3, tblContacts3[#Headers])), tblContacts3[Customer Name]=B4)
@viktorasgolubevas2386
7 күн бұрын
@@ExcelCampus Also, in case your target non-adjacent header is an ordered (important) subset of the source header, I'm using COUNTIF technique - demo of 2-dimensional slice and dice with FILTER :)) =FILTER(FILTER(tblContacts3, COUNTIF(C3:E3, tblContacts3[#Headers])), tblContacts3[Customer Name]=B4)
@alcol72-0
5 күн бұрын
Is it just me or 0+1 should equal 1 at 12:18? Anyways, thanks Jon for another great video!
@ExcelCampus
4 күн бұрын
You are correct. Typo during the editing process. Sorry about that! Thanks for letting us know. 🙌
@KrisArmstrong-n9o
10 күн бұрын
Love! Love! Love! ❤❤ Thanks!
@ExcelCampus
10 күн бұрын
Thanks Kris! 😊
@vinc.7765
10 күн бұрын
Very useful! Is it possible to have the timestamps?
@ExcelCampus
10 күн бұрын
Thanks for the reminder, Vin! We just added the timestamps for chapters. 👍
@H4XOR23
Күн бұрын
Is there any way I can schedule teams meeting from excel from tables in spreadsheet?
@WaqasAhmed-gj3sq
2 күн бұрын
Which software you use for video effects, editing and transitions?
@ashokgaddam8996
8 күн бұрын
Nice
@SandeepTiwari-dp2wx
8 күн бұрын
Hello Jon, thanks this is really very helpful. But how to get details if my required column are not in a sequence. eg. Contact Name is column c and phone number is column h...so on. Please help.. thank you 🙏
@ExcelCampus
4 күн бұрын
Hi Sandeep, Great question! You can still use CHOOSECOLS and specify the column numbers out of order. For example, you could use 1,4,2 for the column numbers to return the State column before the Phone Number column. I talk about CHOOSECOLS at 5:31 in the video. I hope that helps. Thanks again and have a nice day! 🙂
@nicolebrocke8989
10 күн бұрын
Thank you for this video! I was just trying to use this function for the first time today! Perfect timing! Is there a way to use the filter function with the sort function AND sort the data in a custom order (not ascending or descending)?
@ExcelCampus
10 күн бұрын
Great question, Nicole! Yes, it's definitely possible. When you say custom order, do you want to sort ascending/descending based on another existing column in the source table?
@nicolebrocke8989
9 күн бұрын
Another column in the source table, yes. However, not ascending or descending but rather a specific custom order.
@ExcelCampus
4 күн бұрын
Thanks for clarifying. So, yes, this is possible using the SORTBY function. We need to sort both the array and include arguments withing FILTER first. Basically, we sort then filter. This means SORTBY must be used twice. So the formula looks something like the following. =FILTER(SORTBY(),SORTBY()=B4) This will return the sorted arrays to filter, and filter criteria "=B4" can be applied to the sorted array. To do a custom sort we can use the XMATCH function to look up and return the row number on the custom sorted list. Here's an example formula from the Tables example in the video at 3:54. =FILTER(SORTBY(tblContacts3[[Contact Name]:[State]],XMATCH(tblContacts3[State],P7:P14)),SORTBY(tblContacts3[Customer Name],XMATCH(tblContacts3[State],P7:P14))=B4) I have a unique list of the States in a custom order in P7:P14. XMATCH is looking up each value in the state column in that list and returning the row number to SORTBY. I'm planning a follow-up video on advanced techniques for FILTER and I'll include this example. I hope that helps. Thanks again and have a nice day! 🙂
@nicolebrocke8989
2 күн бұрын
This is very helpful! Thank you for taking the time to respond! I look forward to watching your follow up video.
@sanketv1
8 күн бұрын
Filter is an array function , means it will display #SPILL error if list goes beyond available space below it. Is there a way to restrict the list of rows it displays so we will not get #SPILL error
@ExcelCampus
4 күн бұрын
Great question, Zohar! Yes, we can use the TAKE function to limit the number of rows. =TAKE(FILTER(), 2) That formula will return the first 2 rows from the results of FILTER. You can specify a negative number if you want to return the last rows from the bottom instead. I'm planning a followup video on advanced uses for FILTER and I'll include this example. I hope that helps. Thanks again and have a nice day! 🙂
@oluwapelumiaroboto5382
4 күн бұрын
Please share the link to the file.
@you_are_kidding_me_right
9 күн бұрын
Can a drop down box be done for filtering contacts where you can choose from the drop down?
@ExcelCampus
4 күн бұрын
Great question! Yes, I talk about adding a drop-down list at 3:03 in the video. You can use the same technique for filtering contacts instead of customer/company names. If you want to only display contacts in the drop-down for the selected customer, this can also be done. It's typically referred to as dependent drop-down lists. I'm planning a follow-up video on advanced techniques for FILTER and I'll include this one as an example. I hope that helps. Thanks again and have a nice day! 🙂
Пікірлер: 62