when filter function will be available?

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I just watched a video and ExcelIsFun author said Filter() not not available now. He manged to do a lot of stuff without using array function anymore. That is a big change.

My second question, is Microsoft going to get rid of array formula?

https://www.youtube.com/watch?v=cB6UlIwpwVc
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Another question, I did use exactly the same formula he used but did not work. When I typed the formula in the cell B2 and press enter. It only applied to the first cell (it did not copy over like what happen to him). Then I had to use autoFill to copy the formula over, but also did not work. Then I had to use absolute address like below

=MATCH($B$2:$B$5,$A$2:$A$12,0)

My question, how come for him he did not have to use absolute address? I am also using Office 365

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]list1[/TD]
[TD="class: xl65, width: 64"]list2[/TD]
[TD="class: xl66, width: 64"]new list[/TD]
[/TR]
[TR]
[TD="class: xl66"]mike1[/TD]
[TD="class: xl66"]linda1[/TD]
[TD="class: xl66, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl66"]alex1[/TD]
[TD="class: xl66"]alex2[/TD]
[TD="class: xl66, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl66"]mary1[/TD]
[TD="class: xl66"]mary[/TD]
[TD="class: xl66, align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl66"]sarah1[/TD]
[TD="class: xl66"]alex1[/TD]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]john1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]linda1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]mike2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]alex2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]mary2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]sarah2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]john2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Works fine for me......Place the formula in C2 and drag down
 
Upvote 0
Another question, I did use exactly the same formula he used but did not work. When I typed the formula in the cell B2 and press enter. It only applied to the first cell (it did not copy over like what happen to him). Then I had to use autoFill to copy the formula over, but also did not work. Then I had to use absolute address like below

=MATCH($B$2:$B$5,$A$2:$A$12,0)

My question, how come for him he did not have to use absolute address? I am also using Office 365
See here for more info but an extract:
Note: Dynamic array functions are currently a beta feature, and only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.
 
Upvote 0
Thank you all. Now I did it in my way, using absolute reference and then autofill. I also created a new column using ISNA() function and I got False and True. Now I want to change the last column to name instead of False/True like what Gravin did in his video. Thank you very much

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl66, width: 64"]list1[/TD]
[TD="class: xl66, width: 64"]list2[/TD]
[TD="class: xl66, width: 64"]=MATCH($B$2:$B$5,$A$2:$A$12,0)[/TD]
[TD="class: xl66, width: 64"]isna()[/TD]
[/TR]
[TR]
[TD="class: xl65"]mike1[/TD]
[TD="class: xl65"]linda1[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl65"]alex1[/TD]
[TD="class: xl65"]alex2[/TD]
[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl65"]mary1[/TD]
[TD="class: xl65"]mary[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="class: xl65"]sarah1[/TD]
[TD="class: xl65"]alex1[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="class: xl65"]john1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]linda1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]mike2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]alex2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]mary2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]sarah2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]john2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Is this what you are trying to achieve?

Excel Workbook
ABC
1list1list2
2mike1linda1alex1
3alex1alex2linda1
4mary1maryalex2
5sarah1alex1
6john1
7linda1
8mike2
9alex2
10mary2
11sarah2
12john2
List
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top