Thank you for the great Filter examples Mark. I feel like Filter is such a power function, and there are so many ways to use it. Thank you so much for showing the And(*)/Or(+) logic in example 4 and the dependent drop down in example 5, that was neat.
FILTER is probably one of the most powerful functions that we now have. Hopefully Microsoft will release some more dynamic array functions at some point, and that might make FILTER even better.
Sorry to be so offtopic but does anybody know a way to get back into an instagram account?? I stupidly forgot my login password. I would appreciate any assistance you can give me
@Julio Justice Thanks so much for your reply. I got to the site on google and im in the hacking process now. Looks like it's gonna take quite some time so I will get back to you later with my results.
Thank you for the great Filter examples Mark. I have question .. Example 5# How clear the cell in drop down 2 after select a new country in Drop down 1 !! for example when select a new country want clear the city cell because the old city will appear with the new county !! thanks in advanced Mark
Great video! Is it possible to use the FILTER function on a large table and combine it with COUNTIF to get a count of the number of times something shows up in a row in numeric form?
Hello Marc, thank you for this video. it puts everything in good order !!! can you please explain whether we can FILTER an array which is part of a Pivot Table ? i tried it using the "include" parameter with (*) AND, and it returns a #VALUE. (the same thing happens if i use SUMIFS as a workaround) any idea ? Thanks anyway, Be well, and i wish you the best for your new CONSULTANCY business
Hi Megamundus - sorry for the delayed reply. You can use a dynamic array function over a PivotTable, but it is not dynamic. So if the PivotTable expands the formula does not expand to include the new rows. So probably doesn't provide the functionality that you're after.
Thank you for this helpful video. I have a question: I have a 13x3 dynamic array in C3:E15 containing random integers between 1 and 17. If in some other cell I type "=C3#>7", I get, as expected, a 13x3 array of Boolean values. However, if I try to calculate "=FILTER(C3#, C3#>7)", it returns a #VALUE! error. Why? This doesn't make sense, does it?
There is currently no way to format the resulting array as a table. You could use a macro or copy and paste values (but that's probably not what you're looking for).
@@ExcelOffTheGrid Yep, copy-pasting kind of defeats the purpose of using the dynamic array, one may as well filter the table, then copy and paste. Hopefully we’ll get the option somewhere down the line. Thanks a lot!
@@banachdj Each feature is great in its own right, and can be used for amazing things together. However, getting a dynamic array into a Table isn't one of them. I know the feature has been requested through the old Excel User Voice forum, but I've not heard anything since.
In that scenario, it might be easier to use SORT instead of SORTBY. But if you do use SORTBY the challenge is keeping the arrays in sync. Take a look at my SORT and SORTBY videos for more examples.
@@joeblow9284 If using SORT and FILTER it doesn’t matter which nests inside the other, both will work fine. One way probably calculates faster than the other, but unless you’re dealing with huge amounts of data, you’ll never notice.
To FILTER with wildcards, you will need to use some TRUE/FALSE logic. Look at the solution in this forum: www.mrexcel.com/board/threads/filter-function-with-wildcards.1103803/
You're quickly becoming my second favorite excel guru next to Mike Gervin
Thanks - Mike is certainly a legend. Amazing to be thought of in the same category as him.
Thank you for the great Filter examples Mark. I feel like Filter is such a power function, and there are so many ways to use it. Thank you so much for showing the And(*)/Or(+) logic in example 4 and the dependent drop down in example 5, that was neat.
FILTER is probably one of the most powerful functions that we now have.
Hopefully Microsoft will release some more dynamic array functions at some point, and that might make FILTER even better.
Sorry to be so offtopic but does anybody know a way to get back into an instagram account??
I stupidly forgot my login password. I would appreciate any assistance you can give me
@Kameron Turner instablaster ;)
@Julio Justice Thanks so much for your reply. I got to the site on google and im in the hacking process now.
Looks like it's gonna take quite some time so I will get back to you later with my results.
@Julio Justice it did the trick and I now got access to my account again. Im so happy!
Thanks so much, you saved my account :D
Great video, each example is exactly what the doctor ordered!
Thanks 😀
thank you...
You're most welcome
Excellent explain sir.. thankyou so much sir ❤😊
Thanks you . 😀
Great video. Thanks a lot
Thanks 😊
GREAT Thanks !!
Sorry ... downloading this file doesn't work ...
Thank you for the great Filter examples Mark. I have question .. Example 5# How clear the cell in drop down 2 after select a new country in Drop down 1 !! for example when select a new country want clear the city cell because the old city will appear with the new county !! thanks in advanced Mark
You would need to use a Macro which triggers the worksheet change event when the cell changes.
Thanks for sharing such a great video, Do you think filter function will be on MS2019 any sooner , thanks again and keep the good work.
I'm pretty sure Microsoft announced that it will not be available on Office 2019. I'm guessing they want people to use 365.
Super bro well explained 😍
Thank you 😀
Thanks Sir very helpful
You're welcome :-)
There is a "gotcha." If any of the source data have an error, to formula fails.
Thank you so much. How can we calculate non adjacent columns by using Filter function?
You can use CHOOSE to combine ranges prior to calculation.
Check out this post : exceloffthegrid.com/advanced-dynamic-array-formula-techniques/
Great video! Is it possible to use the FILTER function on a large table and combine it with COUNTIF to get a count of the number of times something shows up in a row in numeric form?
Thanks Awesome feature.
Can we filter data with range values full column 1 ....99 rather than single value.
There is no range filter, but we can FILTER with values >=1 and
Hello Marc, thank you for this video. it puts everything in good order !!!
can you please explain whether we can FILTER an array which is part of a Pivot Table ?
i tried it using the "include" parameter with (*) AND, and it returns a #VALUE.
(the same thing happens if i use SUMIFS as a workaround)
any idea ?
Thanks anyway, Be well, and i wish you the best for your new CONSULTANCY business
Hi Megamundus - sorry for the delayed reply.
You can use a dynamic array function over a PivotTable, but it is not dynamic. So if the PivotTable expands the formula does not expand to include the new rows.
So probably doesn't provide the functionality that you're after.
when using the filter function in VBA use .Formula2 instead of just .Formula
I must admit that I've not tried any dynamic array formulas inside VBA. That's a useful tip to remember.
Thank you for this helpful video. I have a question: I have a 13x3 dynamic array in C3:E15 containing random integers between 1 and 17. If in some other cell I type "=C3#>7", I get, as expected, a 13x3 array of Boolean values. However, if I try to calculate "=FILTER(C3#, C3#>7)", it returns a #VALUE! error. Why? This doesn't make sense, does it?
Great video, neatly explained. Do you know if there’s a way to format the resulting list as a table?
There is currently no way to format the resulting array as a table. You could use a macro or copy and paste values (but that's probably not what you're looking for).
@@ExcelOffTheGrid Yep, copy-pasting kind of defeats the purpose of using the dynamic array, one may as well filter the table, then copy and paste. Hopefully we’ll get the option somewhere down the line. Thanks a lot!
@@banachdj Each feature is great in its own right, and can be used for amazing things together. However, getting a dynamic array into a Table isn't one of them.
I know the feature has been requested through the old Excel User Voice forum, but I've not heard anything since.
Can you combine the filter formula with the sortby formula?? I’ve filtered my data, now I want to organize it
In that scenario, it might be easier to use SORT instead of SORTBY.
But if you do use SORTBY the challenge is keeping the arrays in sync.
Take a look at my SORT and SORTBY videos for more examples.
@@ExcelOffTheGrid Would I nest the sort inside the filter formula? Or the other way around?
@@joeblow9284 If using SORT and FILTER it doesn’t matter which nests inside the other, both will work fine.
One way probably calculates faster than the other, but unless you’re dealing with huge amounts of data, you’ll never notice.
Hello brother if we want to search dynamically like if we want to search Asia and we type a or as it shows results
In =filter formula
To FILTER with wildcards, you will need to use some TRUE/FALSE logic.
Look at the solution in this forum:
www.mrexcel.com/board/threads/filter-function-with-wildcards.1103803/
You are god
Sorry, no. I’m just a simple man trying to share what I know. 😀
Filter Function is not available in Excel 2007 ??
It’s only available in Excel 365 currently.