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.
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.
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.
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
@@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.
Download File - goodly.co.in/crazy-excel-filter-function-hacks-i-wish-i-knew-before/
In choosecol func you may use list style of column numbers: {1,2,3}.
Great tutorial. I use FILTER function extensively. It took me a while to figure out these hacks. Your tutorial provides fabulous explanations.
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.
The formula to copy the header of raw data table is =Data[#Headers].
simply brilliant
Chandeep you are superb in terms of content and clarity u are showing in your videos..Hats off
Thanks Suresh :)
Excellent tips and tricks. These are amazing for somebody who wants to transition from Excel to database thinking.
Fantastic tutorial. I can think of many uses for it. Thank you very much.
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 🤗🤗
Approximate search is awesome bro
Wow, nice tips. Great! Thank you!👍
This is well created. I going to use it.
Thanks Chandeep, super helpful
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
Thanks for the video, but what it you want to choose a range ?
Extremely good hacks
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.
Great session👍
Excellent!!
I wanna choose not all columns, two of them ore mixed ones. What will we do?
Thank you 😊
Loved it!!
Excelente vídeo!
Nice trick with countif..👍
Do you have choosecol video?
Great really helpful.😊
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?
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 ?
Countif was new for me
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.
The syntax for AND and OR seem unintuitive.
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
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.
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
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
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 ruclips.net/video/jLpgt-wptH4/видео.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.