Use the new FILTER() formula and retrieve only the columns you want

Dynamic Array Formulas, Home Page Post

So in my previous post I was banging on about the new Dynamic Array formulas only fairly recently released to Office 365 Excel.  I reckon FILTER() is the star but as I used it, I became a little disappointed as it seemed like you could only return ALL the columns in the range you are filtering. 

Often you don’t want to do this, you want just the columns you need or in a different order or maybe just one column to dovetail into what ever else you are doing.  In the past, I have been an avid user of Excel’s Advanced Filter particularly within a VBA routine to do this kind of thing but that takes a bit of setting up and it’s not a formula, it’s an Excel process.  A formula is quicker to set up on the fly and naturally dynamic compared to VBA or the manual Advanced Filter.

So I wondered… there must be a way to use new FILTER() formula and retrieve only the columns you want.

So my jaw just dropped when I recently saw this video from the never boring Microsoft MVP Oz du Soleil, who shows us you can select only the columns you want.  Wow!  This means a process in Excel (whether done manually or via VBA) can indeed now be done within an Excel FILTER formula.   For me in my work this is huge.  I have since added a draft of the formula to my AHK (Auto Hot Key https://www.autohotkey.com/ ) as “FilterX” which is my equivalent of “that’s going straight to the pool room” aka Darryl Kerrigan from the quirky Aussie comedy film The Castle a simple tale about a Aussie family man.  If you don’t use AHK, it’s great for generating auto replace text and script base shortcuts which works across all PC software.  But you could also enter this into Excel’s autocorrect.

So here is the formula in Oz’s video modified by me a bit.

=FILTER(CHOOSE({1,2,3,4,5,6},A2:A93,B2:B93,H2:H93,C2:C93,E2:E93,F2:F93),D2:D93=$M$11)

Basically it’s selecting 6 columns A,B,H,C,E & F for the rows 2:93 if D2:D93=whats in M11 (which can be a dropdown).  But the main point is it’s returning 6 selected column A,B,H,C,E & F in the order I want.

Anyway watch the ever entertaining Oz explain this and enjoy!