Excel FILTER Function TRICK for Non Adjacent Columns
6 August 2020 Excel Hacks: Tips & Tricks
Are you up for a really cool trick with Excel's new Filter Function? With a simple trick, you can use it to get multiple match results from Non-Adjacent columns in a Simple & Dynamic way. You can also use this trick to get unique values from non-adjacent columns.
So, let's say we have a dataset with 6 columns but we're only interested in the information in column 2 and 5, i.e. in non-adjacent columns. Plus, we don't want to get "all" value from these columns but instead only records that match a certain criteria. And we want it to be in a dynamic way so whenever we change the criteria, the result updates automatically.
I'll show you an Excel trick that get's this done in record time. The secret is to broadcast array constants in our formula. And not only that: You can also use this to get a UNIQUE list of combinations that are in non-adjacent columns.
As a bonus tip I'll show you how you can SORT the result based on a value that's not in the filtered end result.
Many thanks to my student Adam Payne for sharing this method with me!
*** In case you'd like to learn more about Excel Dynamic array functions check out my complete course: https://courses.xelplus.com/p/excel-dynamic-array-formulas
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-filter-trick-non-adjacent-columns
LINK to FILTER video: https://youtu.be/Eehk6PC0oGs
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Microsoft 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
Time Stamps
00:00 Multiple Match Results from Non-Adjacent Columns
01:40 Excel FILTER Function
04:00 Filter Out the Columns You Do NOT need
06:09 Get UNIQUE list from Non-Adjacent Columns
09:10 Bonus Trick for Sorting
GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel #Excel #LeilaGharani
-
0:57
Dexcelo
1 year agoFilter Function #excel #تعليم #microsoft #اكسل #microsoftexcel #office #data #datascience #exceltips
3 -
0:23
Mighty Excel
2 years agoBasic Excel Tips Trick (Select Non Adjacent Cell) Part 3 #Excel #Learn
55 -
12:58
Practical Excel Tips
1 year agoFiltering And Sorting Tips in Microsoft Excel
20 -
1:27
The Excel Zone
1 year agoEXCEL SLICERS - THE SMART WAY TO FILTER DATA
101 -
38:06
Microsoft Excel Full Course
3 years agoExcel 365 Part 11 – Logical Functions
56 -
2:06
FriendlyMan
1 year agoHÃ m Filter trong excel
1 -
0:40
Crypinfo
1 year agoRange Filter
13 -
1:44:58
Excel For Freelancers
3 years ago $0.02 earnedLearn How To Create The Most User-Friendly Filters In Excel You Have Ever Seen [Free Download]
96 -
5:02
user329
3 years agoPIVOT FUNCTION
18 -
0:34
Mighty Excel
2 years agoExcel Functions for Accountants Part 01 (Transpose) #Shorts #20
16