Awesome Video!!!! Before Joe invented the word "Lifting", that old antique book termed it "Function Argument Array Operation" for a function argument that expects a single value but we give it more than a single operation. Amazing Array Formula, Mr Excel : )
So that means if I want to FILTER products by rep Joey, and want to show both the columns, I can use: =FILTER(CHOOSE({1,2}, B2:B227, D2:D227), B2:B227="Joey") And if we want to show columns reordered then we can use CHOOSE({2,1}..... also!
WOW!!!! On the 11th click of "Evaluate" in the Evaluate Formula dialog, my jaw dropped. That is an awesome trick. Can you send me an e-mail to pub at mrexcel dot com? I would love to hear the backstory on this formula. I just became your 500th subscriber.
I thought this would work for a filter function but it only works putting together one column at a time ...if you try to choose({1,2},A1:B1000,D1:G1000) it doesn't work. What I'm trying to do is use filter to return non-contiguous columns. Any idea on how to do this?
I am posting this issue here only because it relates to Dynamic Arrays and perhaps you have insight. When I us a date in the " to include" argument in FILTER (such a >=1/1/2015) I can get it to work only if I use the serial number of the date. Is this a bug, a feature or am I doing something wrong?
How can one change in Excel lists of the same numbers but in different areas on sheet? Example 1 2 3 4 5. Now if I wanted all the 1's to change if I entered 6 and 2's to 10 and so on in place of 1, 2, 3, 4 or 5 what formula would I use rather than going through the entire page changing all of them one by one or using ctrl H?
Thank you for this! I need a drop down from two non adjacent ranges and even though I had figured out that {} had to be involved, I couldn't come up with a formula that worked. Your formula is fantastic! But when I tried to use O2# as the reference for my data validation, I got an error since data validation wants either one column or one row. Turns out you need to first set up your formual so that it only gives you only one column, then create the data validation. If you then expand the range with your formula, data validation works.
Just a note, on an old solution; you could concatenate the values of interest (either with the function, or with &); once done then remove duplicates, just as you show earlier in the video
In the video, you are just typing in cell O2 for the choose/unique formula. Yet, the results populate for all values of B2:B227 and D2:d227 that make the formula true. What did I miss? Also, when I type in the unique function, I get a name error
Hi Mr. Excel.. dang that's cool! Thanks for the clever trick and also for offering your new Straight to the Point book for free download.. very generous and appreciated! Thumbs up!
I assume (can not try it since I do not yet have theses Dynamic arrays from my insider) that you could similarly use CHOOSE to simply reorder the columns in the data set (for example CHOOSE {1,2,etc},D2:D227, B2:B227, etc))
Awesome Video!!!! Before Joe invented the word "Lifting", that old antique book termed it "Function Argument Array Operation" for a function argument that expects a single value but we give it more than a single operation. Amazing Array Formula, Mr Excel : )
So that means if I want to FILTER products by rep Joey, and want to show both the columns, I can use: =FILTER(CHOOSE({1,2}, B2:B227, D2:D227), B2:B227="Joey") And if we want to show columns reordered then we can use CHOOSE({2,1}..... also!
This seems to work as well : =UNIQUE(FILTER(A2:F227,(A1:F1=B1)+(A1:F1=D1)+(A1:F1=E1)))
WOW!!!! On the 11th click of "Evaluate" in the Evaluate Formula dialog, my jaw dropped. That is an awesome trick. Can you send me an e-mail to pub at mrexcel dot com? I would love to hear the backstory on this formula. I just became your 500th subscriber.
I thought this would work for a filter function but it only works putting together one column at a time ...if you try to choose({1,2},A1:B1000,D1:G1000) it doesn't work. What I'm trying to do is use filter to return non-contiguous columns. Any idea on how to do this?
Yeah! It Is a great way to use CHOOSE function.
WOW! That CHOOSE trick is awesome!
An effective way to dynamically ‘concatenate’ arrays.
Thank you sir.
Thank you Geert Delmulle. I loved that tip from Joe McDaid!
I am posting this issue here only because it relates to Dynamic Arrays and perhaps you have insight. When I us a date in the " to include" argument in FILTER (such a >=1/1/2015) I can get it to work only if I use the serial number of the date. Is this a bug, a feature or am I doing something wrong?
How can one change in Excel lists of the same numbers but in different areas on sheet? Example 1 2 3 4 5. Now if I wanted all the 1's to change if I entered 6 and 2's to 10 and so on in place of 1, 2, 3, 4 or 5 what formula would I use rather than going through the entire page changing all of them one by one or using ctrl H?
Thank you for this! I need a drop down from two non adjacent ranges and even though I had figured out that {} had to be involved, I couldn't come up with a formula that worked. Your formula is fantastic!
But when I tried to use O2# as the reference for my data validation, I got an error since data validation wants either one column or one row. Turns out you need to first set up your formual so that it only gives you only one column, then create the data validation. If you then expand the range with your formula, data validation works.
Wish i could click that Like Button a billion more times. BIll you are the "BEST"!!!!
Very clever.
Just a note, on an old solution; you could concatenate the values of interest (either with the function, or with &); once done then remove duplicates, just as you show earlier in the video
Amazing!!! Thanks Mr Excel.
In the video, you are just typing in cell O2 for the choose/unique formula. Yet, the results populate for all values of B2:B227 and D2:d227 that make the formula true. What did I miss? Also, when I type in the unique function, I get a name error
Welcome to the whole new world of Dynamic Arrays. One formula returns many results. Start here: ruclips.net/video/ZmLu0vMRrGs/видео.html
Hi Mr. Excel.. dang that's cool! Thanks for the clever trick and also for offering your new Straight to the Point book for free download.. very generous and appreciated! Thumbs up!
Thank you Wayne Edmondson. I am glad to hear the video was helpful.
I assume (can not try it since I do not yet have theses Dynamic arrays from my insider) that you could similarly use CHOOSE to simply reorder the columns in the data set (for example CHOOSE {1,2,etc},D2:D227, B2:B227, etc))
Yes Richard Hay. That would work!
Thank you for this film :)
Pretty cool formula Bill! :)
We could have also used classic pivot and selecting three columns
Then in format selected repeat data labels
In case formula gets complicated
Thank you Ravi Koushik. In many cases we are creating reports for people who can not create pivot tables.