Thanks to a comment from Gaetan, I realized that the Excel dynamic array function FILTER could be used to filter out columns as well as rows. As long as your second argument evaluates to a one-row array constant, you are good to go. Examples in this video: Filtering to specific column with a hard-coded array constant. Filtering to specific columns using XMATCH or MATCH. Filtering to columns using formulas in a helper row.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2356.
Filter (the function) by Column.
First, a shout-out to Gaetan.
Who posted a comment on a video from 100 videos ago.
Episode 2252.
He said "this formula seems to work well".
I could not figure out this formula at all.
It was using FILTER. I went into Evaluate Formula.
About 11 steps in, all of a sudden, the What to Include argument evaluates to an array constant.
It is telling me which columns I want.
The light bulb went on.
Most of the time, FILTER is giving us certain rows.
But, Gaetan showed me that it could also give us certain columns.
I am going to ask for the FILTER.
I will choose this entire table.
Comma. And then, what do I want to include?
It is going to be an array constant with commas in between.
So I just look at the data.
I don't need Item. So type Zero.
Buyer? Yes I want that. City? No.
State? No. Phone number? Yes.
Sold? No. Paid? No. Cost? Yes.
Quantity? No. Total? No.
Close the array constant with }. Close Paren.
And I get Buyer, Phone, and Cost.
Alright, so that is an example of hard-coding.
But it would also be possible to use a formula.
Say that I just want Buyer and State.
Check out this formula. It has an XMATCH or you could do MATCH.
Look through the headers. Looking for this table of Buyer and State.
That will return either #N/A if it is not found or where it is found.
1, 2, 3? Those are all considered as TRUE.
Wrap that in IFNA to change the #N/A to zero.
And I get State and Buyer.
This is cool. Here is another one.
Here is a schedule for the month.
I only want to show the columns where I am scheduled to see someone that day.
I add a helper row at the top that counts how many appointments there are that day.
Then this formula says equal FILTER of the Schedule, and I want A1 to AF1.
If it is non-zero, I will get the column.
If it is zero, I won't get the column.
The hassle here is that the FILTER function returns zero for empty.
And so I had to hide those zeroes with three semi-colons in the custom number format.
Or, I could have used a LET function.
So, LET A be the FILTER.
Then if A equals 0, give me nothing. Otherwise A.
Either one will work.
Check this out. Right now I have August 4 and 7th. Let's go to August 6 and add an appointment So 1 PM. And then over here, August 6 appears.
I have to extend my number formatting.
This is an awesome trick. I don't know if this was obvious to you. It certainly wasn't obvious to me.
Until I saw Gaetan's formulas. Gaetan: Thanks for posting that comment on YouTube.
If Gaetan doesn't have one of my Excel Guru patches, send me an e-mail. I will be happy to send him one.
Down below this video, please click Like. Subscribe. Ring the Bell.
We are all Excel fans.
When I discover something new and cool in Excel, I want to share it with you.
Click Subscribe so you can be the first to know about it.
My new book, MrExcel 20/20. Seeing Excel Clearly.
Gaetan: thanks for making that book obsolete!
And THIS book obsolete. I will have to update that.
But, I love the new trick.
I want to thank Gaetan for posting that.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel
Filter (the function) by Column.
First, a shout-out to Gaetan.
Who posted a comment on a video from 100 videos ago.
Episode 2252.
He said "this formula seems to work well".
I could not figure out this formula at all.
It was using FILTER. I went into Evaluate Formula.
About 11 steps in, all of a sudden, the What to Include argument evaluates to an array constant.
It is telling me which columns I want.
The light bulb went on.
Most of the time, FILTER is giving us certain rows.
But, Gaetan showed me that it could also give us certain columns.
I am going to ask for the FILTER.
I will choose this entire table.
Comma. And then, what do I want to include?
It is going to be an array constant with commas in between.
So I just look at the data.
I don't need Item. So type Zero.
Buyer? Yes I want that. City? No.
State? No. Phone number? Yes.
Sold? No. Paid? No. Cost? Yes.
Quantity? No. Total? No.
Close the array constant with }. Close Paren.
And I get Buyer, Phone, and Cost.
Alright, so that is an example of hard-coding.
But it would also be possible to use a formula.
Say that I just want Buyer and State.
Check out this formula. It has an XMATCH or you could do MATCH.
Look through the headers. Looking for this table of Buyer and State.
That will return either #N/A if it is not found or where it is found.
1, 2, 3? Those are all considered as TRUE.
Wrap that in IFNA to change the #N/A to zero.
And I get State and Buyer.
This is cool. Here is another one.
Here is a schedule for the month.
I only want to show the columns where I am scheduled to see someone that day.
I add a helper row at the top that counts how many appointments there are that day.
Then this formula says equal FILTER of the Schedule, and I want A1 to AF1.
If it is non-zero, I will get the column.
If it is zero, I won't get the column.
The hassle here is that the FILTER function returns zero for empty.
And so I had to hide those zeroes with three semi-colons in the custom number format.
Or, I could have used a LET function.
So, LET A be the FILTER.
Then if A equals 0, give me nothing. Otherwise A.
Either one will work.
Check this out. Right now I have August 4 and 7th. Let's go to August 6 and add an appointment So 1 PM. And then over here, August 6 appears.
I have to extend my number formatting.
This is an awesome trick. I don't know if this was obvious to you. It certainly wasn't obvious to me.
Until I saw Gaetan's formulas. Gaetan: Thanks for posting that comment on YouTube.
If Gaetan doesn't have one of my Excel Guru patches, send me an e-mail. I will be happy to send him one.
Down below this video, please click Like. Subscribe. Ring the Bell.
We are all Excel fans.
When I discover something new and cool in Excel, I want to share it with you.
Click Subscribe so you can be the first to know about it.
My new book, MrExcel 20/20. Seeing Excel Clearly.
Gaetan: thanks for making that book obsolete!
And THIS book obsolete. I will have to update that.
But, I love the new trick.
I want to thank Gaetan for posting that.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel
