Use CHOOSECOLS and wrap it arround the horizontal array of headers and select the same column numbers as in the CHOOSECOLS wrap around FILTER to line up headers with the chosen FILTER columns.
Hello Chandeep, Amazing examples of filter, these can be further automated using developer text box to get the most out of these... I too wished i had these before 🤗🤗
Amazing Chandeep! Hats off to your knowledge, attitude and the ease with which you explain the concepts. One question for you. How do I generate the column name dynamically? User selects one of the columns from 5 options (cols) and based on that selection the filter function selects that column dynamically
Thank you. Really helpful. Can you kindly assist with the following? I have a table of transactions by customer, payment date, payment amount. I want to extract the customer name, the last payment date and last payment amount. Ignore all other transactions.
It takes curly brackets automatically when I use filter formula on excel file....I saved file and changed the data...the formula doesn't work....any suggestions please
the =filter function doesn't even work on my app, I don't understand why the online version of excel doesn't work the same as the downloaded app version.
Just tried doing this with text column and the formula wouldn't work - turns out Excel does not like dashes (minuses) (and I would suspect "+/*" either in text values). I took out dashes and it did work. Probably could have put quotations around the text to but went the easy way about it.
@@GoodlyChandeep Thank you, I've used your Power Query videos before and M-Code. In this instance I had a report to publish based on a DAX Qry with manual edits inserted, and was curious if a sum and group by could be achieved without Pwr Qry. I used a nested LET,UNIQUE,FILTER, HSTACK,SUMIFS,LARGE, INDEX, FILTER, SORT, & FILTER function to come get there. Seems like there is no easy way to do this in Excel. It would be nice if MS would add a GroupBy function that is not in PowerQry or DAX.
Here's my issue with the filter function. It makes the excel spreadsheet so large that I can't email or share it. You can't even upload it to a Google Drive to share because it's so large, not to mention that it takes a much longer time to open up. I have a spreadsheet that only has 419 rows in it, and 13 columns. And the initial size of it, before I used only one "Filter" command, was 262kb; but after I used only one "Filter" command in it, it increased to a size of 37,837KB. So my question is, is it typical for an Excel spreadsheet to increase dramatically when we use these new functions, because I usually create spreadsheets (especially at work) to share with others and colleagues, but if these new functions are making these Excel spreadsheets so large that you can't do that, then wouldn't it make more sense to use the old methods of massaging the data instead of using these new functions?
I am facing a challenge. I am using the Filter function and using the Filter condition on one of the columns in the source data. However, I do not want to include that filtered column in my output. How do I do that? This is very easy and intuitive in Google Sheets with Query formula but in Excel, it doesn't seem that easy.
There can be two ways.. First one choose columns and you mention all column numbers except that which you don't want. Second wrap this in another filter and use xmatch as filter condition
Easiest way is to wrap your original filter function in another filter function, and put for example {1,0,1} as the include argument in the second function- this would exclude column 2 if there were 3 columns. Eg =filter( filter(Data, Data[Name]=“Chandeep”), {1,0,1} )
@@martyc5674 Yes, thanks. It works that way but I was looking for a formula that other users will understand. 0,1 parameters may not be so intuitive. Again, what happens if we insert a new column in source data, later.
Hi @martyc5674 , something is wrong........I tried exactly as above but it does not work. Can you please check ? I guess, you have to use CHOOSECOLS only instead of the first FILTER functions
@@priyeshsanghvi8424 no need for choose cols- build your filter function returning all columns, the wrap it in another filter function, the include argument should be in the format {1,0,1,0,} where 1s represent columns you want returned and Zeros you don’t want returned. It works- it’s easy- yes there are better dynamic solutions but this is a great starting point.
Download File - goodly.co.in/crazy-excel-filter-function-hacks-i-wish-i-knew-before/
Great tutorial. I use FILTER function extensively. It took me a while to figure out these hacks. Your tutorial provides fabulous explanations.
In choosecol func you may use list style of column numbers: {1,2,3}.
simply brilliant
Use CHOOSECOLS and wrap it arround the horizontal array of headers and select the same column numbers as in the CHOOSECOLS wrap around FILTER to line up headers with the chosen FILTER columns.
Wow. Only you showed how to filter on multiple values from a single field. Is it possible to do a SQL like grouping sum, count etc ?
Chandeep you are superb in terms of content and clarity u are showing in your videos..Hats off
Thanks Suresh :)
The formula to copy the header of raw data table is =Data[#Headers].
Thanks for the video, but what it you want to choose a range ?
Excellent tips and tricks. These are amazing for somebody who wants to transition from Excel to database thinking.
Hello Chandeep,
Amazing examples of filter, these can be further automated using developer text box to get the most out of these...
I too wished i had these before 🤗🤗
Fantastic tutorial. I can think of many uses for it. Thank you very much.
Amazing Chandeep! Hats off to your knowledge, attitude and the ease with which you explain the concepts. One question for you. How do I generate the column name dynamically? User selects one of the columns from 5 options (cols) and based on that selection the filter function selects that column dynamically
Thank you. Really helpful. Can you kindly assist with the following?
I have a table of transactions by customer, payment date, payment amount.
I want to extract the customer name, the last payment date and last payment amount. Ignore all other transactions.
Approximate search is awesome bro
Thanks Chandeep, super helpful
I wanna choose not all columns, two of them ore mixed ones. What will we do?
This is well created. I going to use it.
Great session👍
Wow, nice tips. Great! Thank you!👍
Excellent!!
Extremely good hacks
Thank you 😊
Nice trick with countif..👍
Do you have choosecol video?
It takes curly brackets automatically when I use filter formula on excel file....I saved file and changed the data...the formula doesn't work....any suggestions please
The function is great but I don't understand the use case in real life, any examples from anyone will be great
Can you do approx search for multiple inputs? ie organizations that contain both b and c or even a longer list?
Loved it!!
Great really helpful.😊
Excelente vídeo!
the =filter function doesn't even work on my app, I don't understand why the online version of excel doesn't work the same as the downloaded app version.
Hi Goodly, i am not able to enroll in your course through card please make any other way to buy course
Can you please send me an email at goodly.wordpress@gmail.com
Just tried doing this with text column and the formula wouldn't work - turns out Excel does not like dashes (minuses) (and I would suspect "+/*" either in text values). I took out dashes and it did work. Probably could have put quotations around the text to but went the easy way about it.
Countif was new for me
I enjoy your videos. What approach would you use with Filter to group by columns and sum the results?
Haven't tried grouping rows using FILTER. I'd rather use Power Query for that. See this might help ua-cam.com/video/jLpgt-wptH4/v-deo.html
@@GoodlyChandeep Thank you, I've used your Power Query videos before and M-Code. In this instance I had a report to publish based on a DAX Qry with manual edits inserted, and was curious if a sum and group by could be achieved without Pwr Qry. I used a nested LET,UNIQUE,FILTER, HSTACK,SUMIFS,LARGE, INDEX, FILTER, SORT, & FILTER function to come get there. Seems like there is no easy way to do this in Excel. It would be nice if MS would add a GroupBy function that is not in PowerQry or DAX.
Here's my issue with the filter function. It makes the excel spreadsheet so large that I can't email or share it. You can't even upload it to a Google Drive to share because it's so large, not to mention that it takes a much longer time to open up. I have a spreadsheet that only has 419 rows in it, and 13 columns. And the initial size of it, before I used only one "Filter" command, was 262kb; but after I used only one "Filter" command in it, it increased to a size of 37,837KB. So my question is, is it typical for an Excel spreadsheet to increase dramatically when we use these new functions, because I usually create spreadsheets (especially at work) to share with others and colleagues, but if these new functions are making these Excel spreadsheets so large that you can't do that, then wouldn't it make more sense to use the old methods of massaging the data instead of using these new functions?
It is possible that something else is going on in your spreadsheet. I doubt the file is getting large because of the filter function
The syntax for AND and OR seem unintuitive.
Wow....
I am facing a challenge. I am using the Filter function and using the Filter condition on one of the columns in the source data.
However, I do not want to include that filtered column in my output. How do I do that?
This is very easy and intuitive in Google Sheets with Query formula but in Excel, it doesn't seem that easy.
There can be two ways..
First one choose columns and you mention all column numbers except that which you don't want.
Second wrap this in another filter and use xmatch as filter condition
Easiest way is to wrap your original filter function in another filter function, and put for example {1,0,1} as the include argument in the second function- this would exclude column 2 if there were 3 columns.
Eg
=filter(
filter(Data,
Data[Name]=“Chandeep”),
{1,0,1}
)
@@martyc5674 Yes, thanks. It works that way but I was looking for a formula that other users will understand.
0,1 parameters may not be so intuitive. Again, what happens if we insert a new column in source data, later.
Hi @martyc5674 , something is wrong........I tried exactly as above but it does not work. Can you please check ? I guess, you have to use CHOOSECOLS only instead of the first FILTER functions
@@priyeshsanghvi8424 no need for choose cols- build your filter function returning all columns, the wrap it in another filter function, the include argument should be in the format {1,0,1,0,} where 1s represent columns you want returned and Zeros you don’t want returned. It works- it’s easy- yes there are better dynamic solutions but this is a great starting point.