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
-
41:06
Rethinking the Dollar
45 minutes agoBRICS Decoupling From the Legacy Infrastructure
1 -
LIVE
Midnight's Edge
2 hours agoMore Lord of Rings coming, Robert Downey jr takes on Musk | MEiTM #631
154 watching -
LIVE
The Big Mig™
13 hours agoGlobal Finance Forum From Bullion To Borders Powered By Genesis Gold Group
2,341 watching -
LIVE
Film Threat
2 hours agoVENOM: THE LAST DANCE DELIVERS! | Film Threat Livecast
118 watching -
LIVE
CHiLi XDD
4 hours ago[SF5] F EM UP Friday! Morning's w/ CHiLi Training Edition #CHiLiDOJO
295 watching -
LIVE
Major League Fishing
2 days agoLIVE! - General Tire Team Series: Patriot Cup - Day 2
553 watching -
12:34
RTT: Guns & Gear
16 hours ago $0.30 earnedA Good Budget Pistol Red Dot | Gideon Optics Alpha
4.13K1 -
LIVE
LFA TV
6 hours agoTHE RETURN OF TRUMP! | LIVE FROM AMERICA 10.25.24 11am EST
5,069 watching -
34:05
BonginoReport
6 hours agoEPIC: Trump’s Vision to Abolish the IRS (Ep.72) - 10/25/24
82.4K188 -
1:41:39
Game On!
12 hours ago $8.28 earnedThe WORST officiated TNF game of all time!
32.5K2