Excel FILTER FUNCTION across multiple sheets
ฝัง
- เผยแพร่เมื่อ 5 มิ.ย. 2024
- Learn how to build a formula using the Filter Function that can be used across multiple sheets in EXCEL.
File available for download: www.etsy.com/au/listing/14454...
Other Filter Function Videos can be found here:
=Filter(), =Filter(AND), =Filter(OR), =Filter(CONTAINS)
• NEW - EXCEL FILTER Fun...
=Filter(Rows & Columns):
• EXCEL Filter Rows & Co...
Other playlists:
Short PIVOT TABLE tutorials
• Pivot Table date forma...
2 MINUTE Excel Formula Tutorials:
• Add Months & Years in ...
EXTRACT Series (Extract Numbers, Extract Letters, Extract First Word, Extract Last Word, Extract Any Word)
• EXCEL - Extract ANY wo...
Excel CHARTS & GRAPH Tutorials:
• Conditional formatting...
Thank you! You made it easy to understand
Absolutely brilliant!!!!! exactly what i was looking for - THANK YOU!!!!!
Thanks for the great comment. Glad the video helped
Worked like a charm, thank you!
Excellent, glad it worked well
Finally it worked & reduced a lot of pressure at work thank you so much!!
Thanks for the update! Glad you found a solution and sorted it out. I was trying to research if there were any common issues using the filter formula.
Fantastic practical real world example, thank you!
Thanks for the comment. Glad you found it useful
Thank you for this video, it was exactly what I needed! Your video was straight to the point and no waffle. Thank you very much!!!
That's great to hear. That's exactly how I try and make my videos.
Thank you! I've been looking for this. Amazing video and great presentation.
Hey hey, thanks for the comment. Happy that you enjoyed!
Thank you for this. Although it still took me a couple of attempts to get where I wanted to be, this video certainly helped me find my way there.
Great to hear and nice effort turning what you learned into a solution that worked for you.
You. Saved. My. Life.
Thank you so much❤
Ha, great. Glad to help out.
Great video, thanks!
No problem, thanks for the comment
thank you so much!! it really help me to combine my sheet!
Great to hear. Thanks for writing comment.
Thanks for the wonderful video ...
No problem, thanks for the nice comment
Thank you very much! How can I apply for the multiple workbooks?
THANK YOU VERY MUCH
Hey hey, thanks for the message. Glad you liked it.
Thank you for this video! I have successfully used your formula to get the Selling Price per Unit and the Sales Percentage into one sheet. However, is there a way to get Percentage Mark in the filter. The source is already with the % mark, but it does not come through in the filter
Thank you very much
My pleasure. Thanks for the comment
I would like to suggest adding dynamic search function using VBA, its pretty straightforward but gives you results as you type.
Nice suggestion. VBA script can come in very handy.
I wonder if there any alternative way to do this horizontally, thank you!
Thank you
No problem, thanks for the comment
Very helpful, thanks. I have mirrored the above with my dataset and can get the FILTER function to work when the column/range I am searching within contains text or straight number however when the data in the column is a number which is the outcome of a formula, it does not return any values. For example, imagine you had an additional column for 'Date of Birth' and rather the data in the 'Age' column being input as a number, it was the result of a calculation/formula (say today's date minus date of birth). When you then try to filter the 'Age' column for people aged under 40 (i.e.
This is what I've been looking for, but for some reason I've been having trouble to combine it with filter function, I tried as well to separate the 2 functions unfortunately it didn't work. Any suggestions?
I try to use vstack on google sheet but it doesn't work by using shift across many tab. I need to pull data one by one and it become a long formula. Do you have any suggestions for me?
Thank you, this is very useful!
Is it possible to add one more last name to filter so it would show not just one last name but a few specific ones?
Hey, thanks for watching.
Try and incorporate the AND formula from this video.
th-cam.com/video/EXF1ewsrGfU/w-d-xo.html
how do i show the comments from the original cell in the cell of the filtered results?
This is exactly what I'm trying to study right now. Unfortunately, I dont have the VSTACK function yet.
Hi, yes. It is only on the newer editions of Excel. Excel always bringing out new tricks.
Thank you so much. You explain so good, clear and understandable. I wanted to ask please Is it also possible to do it in Google Sheet? I tried but unfortunately I didn't succeed.
Thanks for watching. Unfortunately google sheets is similar but not quite the same. Sorry I can't help
Hi, I have tried this but it seems Vstack is not in 2021 version of excel. Is there a work around by any chance? TIA
Nice tutorial, may I know if there any alternative way to do this without “filter” function? I am using the old version of excel 😢
Hi there, I do have an old video of how to use advanced filters in older versions. It doesn't show how to do it across multiple sheets, but it might give you some ideas.
th-cam.com/video/bQUESoDXUpw/w-d-xo.html
Thank you very much for this video. Please correct me if I'm wrong but it appears there is a condition attached to the formula which is not obvious; the total number of rows in all the sheets cannot exceed the Excel's row limit. I have data on four sheets with a total combined row number of 1,800,000 rows. The formula only works when the total combined row number is less than 1,048,576.
I have columns "completed date" and "transition to" but it is returning "0" as default even though I formatted it I would not change. these values are taken into my master data tab on which all the data are there.
Great formular, thanks!
And can we show three column after filter such, Example I want to show First Name, Last Name and Age, Not show City
USE FUNCTION CHOOSECOL
LOOK HERE :
=CHOOSECOLS(filter(vstack('2024:2021'!a2:f300);(vstack('2024:2021'!a2:a300)"")*(vstack('2024:2021'!d2:d300)=b1));1;4)
1 FIRST COLUMN, 4 4ND COL, 3 3ND COLS.
BYE FROM BRAZIL
COPY AND PASTE THE FORMULA ABOVE AND SEE THE MAGIC... KKKK
LET function would be useful here too! nice video though
Great suggestion. The LET function comes in very handy. Glad you enjoyed.
So it is pulling data across multiple sheets, great :) ... now give me the FILTER FUNCTION across multiple sheets please :D Filter data on one list and have them filtred on other list as well :))
Not sure if you will see my comment, or anyone else, but is there anyway I can do another criteria? EG search last name and city?
I tried to just do another * and bracket formula but that doesn’t seem to work?
Hi, thanks for watching.
This video might be able to help. It shows how to filter using multiple criteria.
th-cam.com/video/EXF1ewsrGfU/w-d-xo.html
Great video. But how do I add partial search function instead of exact frase search?
Thank you.
Check out this video... In particular, the filter(if contains) section. Maybe you can incorporate it in your formula.
th-cam.com/video/EXF1ewsrGfU/w-d-xo.html
@@datauntelligence Thanks for your quick reply. I am new to excel. this is the formula which I am working on:
=IF(LEN($B$3)=0;" ";FILTER(VSTACK(MennM:Ungdom!A2:D130);(VSTACK(MennM:Ungdom!A2:A130)"")*(VSTACK(MennM:Ungdom!A2:A130)=B3)+(VSTACK(MennM:Ungdom!D2:D130)=B3);"No data found"))
Where do I put filter(if container)? I have tried, but failed.
when i choose few sheets with the shift button, and then the cells i want... it just saying #VALUE! error. i can only choose them individually
how can i fix this? without choosing them separately
HI, is there a way to do as you've done in this video but instead of using * we use OR function? I can't seem to make it work currently. I want to to have the option of selecting a filter (ie: b2, c2, d2 or just b2 etc...) thank you!
Thanks for checking out the video. Not 100% sure about a solution to your problem, but there are some more examples that may help you along the way in this video.
th-cam.com/video/EXF1ewsrGfU/w-d-xo.htmlfeature=shared
Please come back and share if you have success with your problem.
What if you don’t want all the columns but you just want some of the columns from the other sheets?
wrap it in the CHOOSECOLS function
This function is not working in my excel sheet
I'm getting #VALUE error. pls help
That must be frustrating. Have you had any success working out the problem?
I'm not sure what would be causing your issue.
Here's my issue with the filter function. It makes the excel spreadsheet so large that I can't email or share it. You can't even upload it to a Google Drive to share because it's so large, not to mention that it takes a much longer time to open up. I have a spreadsheet that only has 419 rows in it, and 13 columns. And the initial size of it, before I used only one "Filter" command, was 262kb; but after I used only one "Filter" command in it, it increased to a size of 37,837KB. So my question is, is it typical for an Excel spreadsheet to increase dramatically when we use these new functions, because I usually create spreadsheets (especially at work) to share with others and colleagues, but if these new functions are making these Excel spreadsheets so large that you can't do that, then wouldn't it make more sense to use the old methods of massaging the data instead of using these new functions?
Hey @edcooper. Loving the passion you have for Excel. It can be a frustrating beast sometimes.
I'm not having the same issue as you with the size of files though. The file I used for this example is only 68KB and that is after I filtered the multiple pages.
There could be something else causing the issue. Is there anyone out there that has any suggestions?
@@datauntelligence Could I send you my excel spreadsheet, e.g., upload it to my Google Drive and then send you the link to download and edit it, and do a filter function and see if you get the same results or see what the issue is?