Using The VSTACK And CHOOSECOLS Functions With The FILTER Function In Excel
Вставка
- Опубліковано 19 жов 2024
- In this tutorial we are going to look at how to filter the data, include headers and only select certain columns in a unique order using Microsoft 365 functions FILTER, VSTACK and CHOOSECOLS.
To download the file and follow along, go to the link here:
app.box.com/s/...
Do you need help with a formula or an Excel project?
You can send me an e-mail to mrempel@excel-bytes.com
Including a sample file is always helpful!
You can donate to my channel through any of the following:
PayPal:
paypal.me/Exce...
Zelle or Venmo:
Send to mrempel@excel-bytes.com
Thank you Excel Bytes!!! I spent many hours trying to solve for a count unique entries within a vstack and after many, many videos I was finally able to resolve. =COUNTA(UNIQUE(FILTER(VSTACK(LocalAccts[Rep],RegionAccts[Rep]),VSTACK(LocalAccts[Div],RegionAccts[Div])="Div1,"")))
I'm glad you were able to figure it out!
Thank you for this helpful video. I found it in a search when I was using Vstack in a filtrered table and got undesirable results. This vid and you saved the day!
I'm glad to hear that you found this useful!
Fantastic friend
Wow..been +1year but I just know it..thanks
thanks bro
I have used FILTER extensively since it first became available. Depending on the setup I have used it sometimes in the array argument of FILTER, other times in the "to include" FILTER argumentand yet on other occasions as a FILTER wrap as you did here. CHOOSECOLS has also replaced the previously used FILTER(FILTER) constructon as well as the FILTER(Array,{1 0,0,1,0 1} type of formulation. All 3 are great functions!!
Thanks Mike!
👍
Very helpful 👌
Thanks
thank you, great video
Thanks!
This is so cool.
Thanks! I'm glad you liked it.
this was just what I was looking for. Is there a way to carry over the formatting of those cells by any chance?
Sorry, none of these functions has an option to include formatting
Thanks 😊
👍
Thanks Excel Bites. This is the exact requirement for me, but i have only 2021 version in my laptop. What is the alternate formula for Excel 2021 for the same requirement? Please let me know
You can't do it all without 365, but this tutorial might help: ua-cam.com/video/bcXBQxp0Vyk/v-deo.html
Thank you for this example; however, I have a question. Suppose that one of the columns that you want was the Date column. It is not coming back formatted as a Date. How would you do this and get the Date column formatted properly?
Sorry, Excel functions only return data, not formatting. Formatting is a function of the destination cells, so you will need to format those after running the FILTER function.
Can use Choosecols by Microsoft office Excel 2019?
I dont get CHOOSECOLS in Excel 2019
This only for Excel 365?
It's only available in Microsoft 365 or the web version.
If there are two sales tables in different cores I want to use a filter function to search for the employee's name in the two tables . many thanx
Only way I could think of is if you have Microsoft 365, you can use the VSTACK function. Here is a formula example: =VSTACK(FILTER(Table1,Table1[Name]="Tom"),FILTER(Table2,Table2[Name]="Tom"))
@@ExcelBytes I did it but the formula not responds. Why.many thanx
@@hanyfahmy7021 I'd have to see it to be sure. Can you send me a sample of your file? You can send to mrempel@excel-bytes.com
@@ExcelBytes nany thanx it is work. Thanx my reg