Last week I encountered a similar problem when I was building a table (not an Excel-table) with calculations, using the FILTER-function. There came a few SPILL!-errors and in this particular case the solution was wrapping the FILTER-function inside SUM-function. It gave eveywhere ONE result and it was exactly what I needed. Thanks for your other ideas: very helpfull!
Both methods are simple and effective. The TEXTJOIN method is a nice way to display multiple text values as in this scenario while the INDEX or other aggregations like MIN & MAX would allow choosing one value among the list. Thanks for sharing, Jon. Best wishes.
Very good video, as usual. Next topic could be the following: since tables can't be used as "primary container" of a dynamic array function, I would like to know how to add custom columns beside a dynamic array results and make the formulas in these columns to adapt to the depth of the dynamic array, i.e. that these extras columns will add/remove rows according the dynamic array results.
@@flashgenai Currently, I am using the regular Excel capability to expand formulas down and when the result is smaller than previously I have to cut the extra rows.
Thanks Kim! Unfortunately, the TRANSPOSE function will still return a #SPILL error when used in an Excel table. In that case the spill error is caused by the spill range spanning multiple columns instead of rows, even if the table has additional blank columns.
@@ExcelCampus You could use the concat function before the transpose. But your way is better because you are going to need some sort of separator anyway.
Great video. I must admit, I had just applied the rule of ‘dynamic arrays don’t work with tables’. But clearly that’s not true if we can limit to 1 result. Hmmm… 🤔… I wonder what we can use this for.
It was great. One solution is to use the difference in the number of columns in the INDEX function as follows, in which case there is no need to use the COUNTIF and MAX functions.=IFERROR(INDEX(FILTER(tblCustomers[phone number],tblCustomers[Customer ID]=[@[Customer ID]]),COLUMN()-COLUMN([Name])),"")
When working with league schedules, is there an easy way to pull all the Home Teams (with an "@" before their team name) from the week column, remove the @ symbol and spill the results into an array without blank spaces?
Thanks for this useful video! I'm trying to pass through the results of a FILTER function to a COUNTIF, but all I'm getting is "There's a problem with this formula", despite the results of the FILTER appearing to be in the same format as a column range i.e {"1";"2";...}. I use the fx button and see it just returns a blank result, is there any reason why this happens or workarounds?
The index with filter formula can return all values right? So if the Vlookup only can return 1 value and Xlookup can return 2(with -1 in the order last to one) your example can return all values? Now we dont need "for" and "if" with vba for multiple result!!? Thanks, very good job!!
Hi Jose, Great question! Unfortunately, TRANSPOSE still returns a spill error if the formula is inside the table. The table will not extend to include any new columns produced by TRANSPOSE's spill range. Even if you add new blank columns, it still returns an error. At least this time. Maybe that will change in a future update.
I love the video and the concept. I tried to do this with 1500 rows and a array of only 2 columns being returned. It pretty much froze for 30 sec every time I changed the filter. I have an 8th gen i5 with 32gb of ram. Does anyone else have these problems? And for me 1500 rows in small for me. I'm usually working with 20k or more rows.
Not working for me. Same conditions as your example. Same code and same formula... only returning the first value in my table of my original array into my new table
I am intrigued. Excel Tables are designed NOT to support dynamic array formulas and yet, simply by adding TEXTJOIN or INDEX and so on, the FILTER function works in your Excel Table. I have tried to use, eg, the UNIQUE function inside an Excel Table but to no avail ... is there something else we need to know?
Pardon the late reply. Are you thinking about adding contents to a table from a UNIQUE-treated array? If that's what you mean, I do this sometimes, by having this in my table: =INDEX(UNIQUE(myarray);1) Sorry if I misunderstood you.
I just saw a few other comments that others were experiencing the same behavior. This could be due to a recent update that I wasn't aware of. I'm looking into it. Sorry about that.
I really hope they add spilled range behavior to tables, especially with BYROW, LAMBDA, and other new functions
This is a great work around for now
I agree. Thanks Patrick! 🙂
Last week I encountered a similar problem when I was building a table (not an Excel-table) with calculations, using the FILTER-function. There came a few SPILL!-errors and in this particular case the solution was wrapping the FILTER-function inside SUM-function. It gave eveywhere ONE result and it was exactly what I needed. Thanks for your other ideas: very helpfull!
Both methods are simple and effective. The TEXTJOIN method is a nice way to display multiple text values as in this scenario while the INDEX or other aggregations like MIN & MAX would allow choosing one value among the list. Thanks for sharing, Jon. Best wishes.
Great point on MIN & MAX! Thanks Dinesh! 🙂
Thank you! You have a great day too! You’re great ! Love your channel!🌹
Very good video, as usual. Next topic could be the following: since tables can't be used as "primary container" of a dynamic array function, I would like to know how to add custom columns beside a dynamic array results and make the formulas in these columns to adapt to the depth of the dynamic array, i.e. that these extras columns will add/remove rows according the dynamic array results.
@Jacque Doyon Did you ever figure out how to do this?
@@flashgenai Currently, I am using the regular Excel capability to expand formulas down and when the result is smaller than previously I have to cut the extra rows.
Thanks John for the tips 👍
I Just Learned Something New With These Great Tips..Thank You Jon :)
Thank you Darryl! 🙂
Hi John, thanks for the tips. I actually wrap the FILTER function with TRANSPOSE … it works well for me 🙂
Thanks Kim! Unfortunately, the TRANSPOSE function will still return a #SPILL error when used in an Excel table. In that case the spill error is caused by the spill range spanning multiple columns instead of rows, even if the table has additional blank columns.
@@ExcelCampus You could use the concat function before the transpose. But your way is better because you are going to need some sort of separator anyway.
Thank you... Saves a lot of time
Great tut, thank you.
Nice tips Jon! Thanks for sharing! 😊👍
Nice Jon. Great tips! Thanks for sharing :)) Thumbs up!!
Thanks a lot, simple and clear and very helpful
😀😀
This was awsome man, Thanks,
Can we try to use TOROW with FILTER function to avoide INDEX funtion?
Great video.
I must admit, I had just applied the rule of ‘dynamic arrays don’t work with tables’. But clearly that’s not true if we can limit to 1 result.
Hmmm… 🤔… I wonder what we can use this for.
Even though I didn't have a need for this yet, I have a strong feeling that I will probably need to do this for a customer at some point in my life. 😀
Haha awesome! Thanks Rayno!
Thanks that was helpful for me
You're welcome, Mukhtar! :)
Awesome Solutions John :) you always bring unique ideas/ Solutions in your tutorial. Thank you for your hard work :)
Thanks Nader! I appreciate your support. 🙂
which video editing and screen recording software are you using? The video looks so clean.
Hi John, I request you to make video regarding effective use of excel on mobile version .
Thanks Sakthivel. We'll add it to the list for future videos.
It was great. One solution is to use the difference in the number of columns in the INDEX function as follows, in which case there is no need to use the COUNTIF and MAX functions.=IFERROR(INDEX(FILTER(tblCustomers[phone number],tblCustomers[Customer ID]=[@[Customer ID]]),COLUMN()-COLUMN([Name])),"")
When working with league schedules, is there an easy way to pull all the Home Teams (with an "@" before their team name) from the week column, remove the @ symbol and spill the results into an array without blank spaces?
Thanks for this useful video! I'm trying to pass through the results of a FILTER function to a COUNTIF, but all I'm getting is "There's a problem with this formula", despite the results of the FILTER appearing to be in the same format as a column range i.e {"1";"2";...}. I use the fx button and see it just returns a blank result, is there any reason why this happens or workarounds?
The index with filter formula can return all values right?
So if the Vlookup only can return 1 value and Xlookup can return 2(with -1 in the order last to one) your example can return all values?
Now we dont need "for" and "if" with vba for multiple result!!?
Thanks, very good job!!
Is there any alternative formula or VBA solution in Excel 2019 for Filter formula which is only available in Excel 365?
I am using Excel 2019, and it seems my Excel don't recognize the filter function, any help?
Good tips. Just one question. Wouldn’t it work if you simply use TRANSPOSE on the filter function?
Hi Jose,
Great question! Unfortunately, TRANSPOSE still returns a spill error if the formula is inside the table. The table will not extend to include any new columns produced by TRANSPOSE's spill range. Even if you add new blank columns, it still returns an error. At least this time. Maybe that will change in a future update.
@@ExcelCampus Many thanks for your reply. Great tips to be used at work in any case. First time I see dynamic arrays in official tables.
Perfect
Requesting you please explain trim formula. Im unable to exicute 100% some valuse showed space
Hi Subhash! We covered TRIM in last week's video. Here is a link. ruclips.net/video/zNLZcqOeMcw/видео.html
I hope that helps.
Another way is to use theTranspose function at the beginning of the filter function, but the data must be converted to range
I love the video and the concept. I tried to do this with 1500 rows and a array of only 2 columns being returned. It pretty much froze for 30 sec every time I changed the filter. I have an 8th gen i5 with 32gb of ram. Does anyone else have these problems? And for me 1500 rows in small for me. I'm usually working with 20k or more rows.
Not working for me. Same conditions as your example. Same code and same formula... only returning the first value in my table of my original array into my new table
I am intrigued. Excel Tables are designed NOT to support dynamic array formulas and yet, simply by adding TEXTJOIN or INDEX and so on, the FILTER function works in your Excel Table. I have tried to use, eg, the UNIQUE function inside an Excel Table but to no avail ... is there something else we need to know?
Pardon the late reply. Are you thinking about adding contents to a table from a UNIQUE-treated array? If that's what you mean, I do this sometimes, by having this in my table: =INDEX(UNIQUE(myarray);1)
Sorry if I misunderstood you.
I tried with dynamic array “unique” on excel table, but still #spill error :(
Both of the techniques will work with the UNIQUE function. You will have to use either the TEXTJOIN or INDEX functions around UNIQUE.
I just saw a few other comments that others were experiencing the same behavior. This could be due to a recent update that I wasn't aware of. I'm looking into it. Sorry about that.
@@ExcelCampus Hi Jon, were you able to find a solution for Unique?
😎
any good souls out there can help the following: sum up above cells until 1st empty cell
Activecell.select .....?????