Just wanted to say THANK YOU SOOO MUCH !!!! Spent 2 days looking for the information how to solve my task, and only your videos gave a clear answer and understanding.
Is there a workaround to view data from a range using filter function but also to be able to change data in the filtered data row/col ? We have sheets with thousands of rows so we use filter function in a parallel sheet to get to the values we wish to analyze. But often we also need to change a value in those filtered values which means going back to the master sheet, search for the row and then modify the value / values I'm sure there must be a better way 😉 Thank you for your efforts in making some extraordinary good tutorials
To do a short cut on a array "Ctrl+Shift+Enter. This was a very good video. I learned a few new tricks. For and "OR" I would use a column and get my true false. then filter it off that. Everything was very understanding. Now I have to learn a few new things. Thank you
very nice video. how do you do it if the second condition is on a different column? for example: column A is where the unique values, column B is the lead name, column C is the product. what I want to do is to get all the values in column A, given the condition set is from column B and column C
14:01 This is the working equivalent =ARRAYFORMULA((B2:B100="reporter")+(B2:B108="Truck Driver")) for this non-working =ARRAYFORMULA(OR(B2:B100="reporter",B2:B108="Truck Driver")) . Use + and *, for OR AND the condition in the FILTER function.
In case you still want to know after 2 years (or if anyone else wonders): You can use {} notation like in Excel. For example, {A:A, C:C} returns columns A and C. Your condition remains unchanged. Example: =FILTER({A:A,C:C}, VALUE(E:E)>5). This displays the rows in columns A and C when the value in column E of the corresponding row is larger than 5.
Im trying to use filter functoon to filter dates. Like i want only the dates showing yestertday to show. However when i try to use this, it is not working. Can you kindly help me? Format of the date in my report is ex: 01-Feb-2023.
I have cells in table B that equal cells in Table A... a filter is applied to Table A and all the numbers get rearrange, because of this the values in Table B also change, I don't want the values in table B to change, i want the cells in table B that i have linked to table A to always show the original values of the cells they were linked to regardless of the filtered arrangement... so my question is how do I keep the cells in table B that have been linked to table A to follow the original values before the filter was applied?
Hi there, I need some help. I'm using this filter function formula and added multiple conditions within that formula (To filter by Dates and Names). I even connected the conditions to respective cells, so I can change the filtered data without touching the formula. However, I have some cases where I just want to filter by dates, to see all the names available under these dates. But its returning error #N/A No matches are found in FILTER evaluation. How do I make it such that if my cell connected to the isnumber condition is empty, the filter will ignore this condition.
@@ExcelGoogleSheets Hi there, thanks for replying. I tried doing this, but i got the error "Filter has mismatched range sizes. Expected row...." What I did was Filter(Range, if(A1=" ", TRUE, isnumber(MATCH(B1:B5, {A1},0))) where A1 being the reference cell for the condition and B1:B5 is the range of Names
Just a heads up: I was not being able to use FILTER cos my default language was set to Portuguese (Brazil). After I change to English, The FILTER function was working properly
No clue. I doubt there is any difference. In all honesty I don't use Google Sheets for large data-sets, it's just too slow for me. But then I guess we'll have to define "large".
So, I understand that you can't filter by something in a ROW and then a COL or vice versa in the same formula, but is there an away to filter the array which is the result of a filter formula, or do you need to "print" the results somewhere, and then filter those?
Yes, you can simply add the formula within a different formula. That being said, FILTER function can only filter vertical data. You could use TRANSPOSE function to make the results vertical though.
@@ExcelGoogleSheets But how do you tell it what to target within the array, as an array obviously doesn't contain column/row references anymore. Also you're wrong about only being able to FILTER vertically, the first pass I'm running is filtering horizontally, as the data has alternating rows assigned to different names and it's returned only the rows with the correct names just fine. I can also set up a FILTER to filter the data by the alternating column headings just fine, and that's in fact what I've done, with the aid of a helper page, I take the already filtered rows and then filter by column to get just the data I want. I would just really like to do it all in one formula without the helper.
Is there a way to use filter and get only specific columns from the source range? For example, in your case, let's say I only want the A and the C column but not B
Very interesting, thank you very much. I’m trying to do these while referring data from another Sheet (data from Google Forms which I don’t want to touch) but it doesn’t work. Any hints? Thanks! The formula I’m using, for reference: =FILTER('Responses'!A2:A59, 'Responses'!B2:B59=A2)
@@ExcelGoogleSheets Thanks for your reply. I mean something like that: docs.google.com/spreadsheets/d/1hoE3pOVmJak5Ia2dt8Iyj7sQ74Icss7B6PR0F2iNX5k/edit?usp=sharing
How will i limit how many results it will show. Example, there is 10 truck drivers that phone numbers starts with 301 but i want to get the first 5 results only
@@ExcelGoogleSheets In custom array read it as a mult-sheet array (my bad). Imagine you're at A1 and trying to filter a mult-sheet array like that: =FILTER({Page1!A1:A10\Page2!A1:A10};A1:A10 > 8) => How would I reference the FILTER function in the result of that array? In QUERY I can just simple use something like "SELECT * WHERE Col1>8". Do you get the picture? Thanks in advance for your attention, it's awesome being able to interact with you.
@@ExcelGoogleSheets Yeah, that's what I thought, we've got to repeat the whole array, changing the column index. Sometimes I work with like 22+ branches worksheets and it's a nightmare changing one by one. Thanks a bunch for your reply!
Hi there, thank you for making this video it really Helps. However I have some problem though. On the last part of the tutorial Filtering the year when I tried making some changes it gives me an error =filter(B2:C108,C2:C108>2010) it says: (Error Array result was not expanded because it would overwrite data in D11. )But if I will change the 2010 to 2014 it works fine.
got it resolved. It seems like there's a space on D11 that's why. So just make sure if you have same problem remove any spaces on the column where you are filtering the data. :)
@@rockydeles1614 That must've been tricky; there seems to be nothing in there, so what am I overwriting?! But first, I would've tried this in different positions to see if it's the row 11 or D-column that has something off with it. If it works in E11, and it works in D12, then it must be cell D11 and you should try deleting it. Diagnostics can be tricky, but trying to isolate the issue by changing one variable at a time works in situations like this.
Hi Sir - I am challenging an error with my Google sheet. I am getting an error "You can't vertically merge cells that intersect an existing filter" I have already removed all filters from the sheet. But still unable to merge cells. Could you please help me?
Thank you bro,, I have a question, what if filter by date with references cell mergecell ? A | B 1 2020/01/02 $20 2 $400 3 2020/01/04 $100 4 $32 5 $55 6 $32 7 2020/01/10 $100
Assuming the date you're searching for is in E2 cell, this should work =ArrayFormula(OFFSET($A$1,match(E2,A:A,0)-1,1,iferror(MATCH(TRUE,(INDEX(A:A, match(E2,A:A,0)+1):A10000)"",0),10000)))
@@ExcelGoogleSheets webapps.stackexchange.com/questions/148136/filter-by-merged-cells-in-google-sheets How do you think you can solve this problem? I tried to apply it but failed
I’m looking for na automatic sum script in Google Sheets... Similar to this in Excel VBa... If I can make a vídeo with something in this Direction. Sub Autosoma() Dim stgUltimalinha As String Dim stgAutosoma As String stgAutosoma = "A" & Range("A" & Rows.Count).End(xlUp).Row - 1 stgUltimalinha = "A" & Range("A" & Rows.Count).End(xlUp).Row Range(stgUltimalinha).Value = WorksheetFunction.Sum(Range("A2:" & stgAutosoma)) End Sub
TH-cam should have a "Love" button. Excellent video!
Just wanted to say THANK YOU SOOO MUCH !!!! Spent 2 days looking for the information how to solve my task, and only your videos gave a clear answer and understanding.
Such a amazing method. However you are more amazing. Your teaching method is phenomenal!
All your videos are incredible. Phenomenal teacher!
Your way of teaching is really smart.
Thank you.
everytime i watch every single video of you , everything looks so simple.
excellent explanation of many hidden features of spreadsheet formulas. It makes my life easier and many things are doable now.
Excellent work!
You remind me of Sal Khan in his early days, outstanding teaching method
So much appreciated
Cool, thanks!
Is there a workaround to view data from a range using filter function but also to be able to change data in the filtered data row/col ?
We have sheets with thousands of rows so we use filter function in a parallel sheet to get to the values we wish to analyze. But often we also need to change a value in those filtered values which means going back to the master sheet, search for the row and then modify the value / values
I'm sure there must be a better way 😉
Thank you for your efforts in making some extraordinary good tutorials
To do a short cut on a array "Ctrl+Shift+Enter. This was a very good video. I learned a few new tricks. For and "OR" I would use a column and get my true false. then filter it off that. Everything was very understanding. Now I have to learn a few new things. Thank you
Looking for the same....
Best explanation ever
Full of nice tips
This video really helps me what I want. Thank You
Very very helpful 👍. Thank you so much 🥰.
Very helpful - THANK YOU!!
Thanks. Going play around with it. My most common challenge is filtering/matching/extracting data from 2 or more sheets.
Thank you a lot, the clip is long but very thoroughly shared
You are a great teacher. Thanks for the video!
very nice video. how do you do it if the second condition is on a different column? for example: column A is where the unique values, column B is the lead name, column C is the product. what I want to do is to get all the values in column A, given the condition set is from column B and column C
One of my favourite functions
:)
Saving this play list
So on Google Sheets I have this:
=iferror(FILTER('RAW DATA 2'!$B$2:$G,'RAW DATA 2'!$B$2:$B>=$B$1,'RAW DATA 2'!$B$2:$B
Really good video. Working with a practical dataset helped make it clear.
Thank you for your lesson! It is much appreciated!
👍
This tutorial are awesome, thank you bro, it helped me a lot, thanks again
Thanks for including the practice file!
straight to the point video! love it.
Thank you for sharing practice sheet, much needed
Very useful video !! Thank so much
Hi, can you create a video about this filter + textjoin. your textjoin vid helped me alot at work. thanks
14:01
This is the working equivalent
=ARRAYFORMULA((B2:B100="reporter")+(B2:B108="Truck Driver"))
for this non-working
=ARRAYFORMULA(OR(B2:B100="reporter",B2:B108="Truck Driver")) .
Use + and *, for OR AND the condition in the FILTER function.
For AND it's best to use *
@@ExcelGoogleSheets fixed, sealed, thanks for the great video tutorials;)
thanku, this is what im looking at.
Got the answer I was looking for in the first 19 Seconds. Briiliant, thanks :)
Love the video thank you. How do you return specific columns in the filter function i.e columns A and C excluding B.
In case you still want to know after 2 years (or if anyone else wonders): You can use {} notation like in Excel. For example, {A:A, C:C} returns columns A and C. Your condition remains unchanged. Example: =FILTER({A:A,C:C}, VALUE(E:E)>5). This displays the rows in columns A and C when the value in column E of the corresponding row is larger than 5.
you are the best
You are the best! Great video! It helped me a lot ! thank you , sir!
Thank you very much for this awesome video
Im trying to use filter functoon to filter dates. Like i want only the dates showing yestertday to show. However when i try to use this, it is not working. Can you kindly help me? Format of the date in my report is ex: 01-Feb-2023.
Thank you very much !!!
I did just as you instructed but it's coming back with #N/A No matches are found in FILTER evaluation. Any ideas? And thank you for the videos!
I have cells in table B that equal cells in Table A... a filter is applied to Table A and all the numbers get rearrange, because of this the values in Table B also change, I don't want the values in table B to change, i want the cells in table B that i have linked to table A to always show the original values of the cells they were linked to regardless of the filtered arrangement... so my question is how do I keep the cells in table B that have been linked to table A to follow the original values before the filter was applied?
Thank you Sir, how to use all this with another sheet ? Is it possible to search with on-click filter function ?
Hi there, I need some help.
I'm using this filter function formula and added multiple conditions within that formula (To filter by Dates and Names). I even connected the conditions to respective cells, so I can change the filtered data without touching the formula.
However, I have some cases where I just want to filter by dates, to see all the names available under these dates. But its returning error #N/A No matches are found in FILTER evaluation. How do I make it such that if my cell connected to the isnumber condition is empty, the filter will ignore this condition.
if(A1="",TRUE,condition)
@@ExcelGoogleSheets Hi there, thanks for replying. I tried doing this, but i got the error "Filter has mismatched range sizes. Expected row...."
What I did was Filter(Range, if(A1=" ", TRUE, isnumber(MATCH(B1:B5, {A1},0))) where A1 being the reference cell for the condition and B1:B5 is the range of Names
Filter(Range, if(A1="", MAP(B1:B5,LAMBDA(_,TRUE)), isnumber(MATCH(B1:B5, {A1},0)))
@@ExcelGoogleSheets Heyy there! Followed exactly and this works! Thank you so much! :)
damn.. you are so good
very nice.. love u
This rocked!
Just a heads up: I was not being able to use FILTER cos my default language was set to Portuguese (Brazil). After I change to English, The FILTER function was working properly
I just wish the video was clearer so I could see the characters he was typing. Being blurry is an eye strain.
Switch to higher res version. It's 4K.
What happens if the column I want to link over has a formula in it? My one comes up with the error #value
Really good thank you
Can the query formula do whatever the filter can do?
Blessed be!
So for larger datasets is it better to use Filter or Query function?
No clue. I doubt there is any difference. In all honesty I don't use Google Sheets for large data-sets, it's just too slow for me. But then I guess we'll have to define "large".
@@ExcelGoogleSheets Hi. Thanks a lot for your videos. What do you use for large data-sets?
I think their answer shows that they use QUERY! (I *think* FILTER is way faster, but that's just perception, I haven't tested it)
So, I understand that you can't filter by something in a ROW and then a COL or vice versa in the same formula, but is there an away to filter the array which is the result of a filter formula, or do you need to "print" the results somewhere, and then filter those?
Yes, you can simply add the formula within a different formula.
That being said, FILTER function can only filter vertical data. You could use TRANSPOSE function to make the results vertical though.
@@ExcelGoogleSheets But how do you tell it what to target within the array, as an array obviously doesn't contain column/row references anymore.
Also you're wrong about only being able to FILTER vertically, the first pass I'm running is filtering horizontally, as the data has alternating rows assigned to different names and it's returned only the rows with the correct names just fine.
I can also set up a FILTER to filter the data by the alternating column headings just fine, and that's in fact what I've done, with the aid of a helper page, I take the already filtered rows and then filter by column to get just the data I want. I would just really like to do it all in one formula without the helper.
Is there a way to use filter and get only specific columns from the source range? For example, in your case, let's say I only want the A and the C column but not B
You can use an array for range, ex: =FILTER({A:A,C:C},B:B="Handsome")
🙏🏻
개꿀팁 👍🏻
Very interesting, thank you very much. I’m trying to do these while referring data from another Sheet (data from Google Forms which I don’t want to touch) but it doesn’t work. Any hints? Thanks!
The formula I’m using, for reference:
=FILTER('Responses'!A2:A59, 'Responses'!B2:B59=A2)
The formula looks correct. You must be making a mistake matching your data to your condition.
@@ExcelGoogleSheets Thanks, I’ll have another look then.
Thanks for the video. How to filter by column and row in the same time (e.g first condition: A2:A5=2, second condition: A2:D:2=3)?
FILTER only filters by columns. What are you trying to accomplish?
@@ExcelGoogleSheets Thanks for your reply. I mean something like that: docs.google.com/spreadsheets/d/1hoE3pOVmJak5Ia2dt8Iyj7sQ74Icss7B6PR0F2iNX5k/edit?usp=sharing
Why not just use index/match?
@@ExcelGoogleSheets Thank you! it works.
Hey Is There Any Way To Use This In And Inportrange
how to take data range from multi sheets thank you
How will i limit how many results it will show. Example, there is 10 truck drivers that phone numbers starts with 301 but i want to get the first 5 results only
try ARRAY_CONSTRAIN function
Can we develop an advanced search cell?
I don't know what you mean by advanced search.
Is there a way to use FILTER function with a custom array? Or in that case using QUERY would be more simple?
What you mean by "custom array"?
@@ExcelGoogleSheets In custom array read it as a mult-sheet array (my bad). Imagine you're at A1 and trying to filter a mult-sheet array like that: =FILTER({Page1!A1:A10\Page2!A1:A10};A1:A10 > 8) => How would I reference the FILTER function in the result of that array? In QUERY I can just simple use something like "SELECT * WHERE Col1>8". Do you get the picture? Thanks in advance for your attention, it's awesome being able to interact with you.
FILTER({Page1!A1:G10;Page2!A1:G10};{Page1!A1:A10;Page2!A1:A10} > 8)
@@ExcelGoogleSheets Yeah, that's what I thought, we've got to repeat the whole array, changing the column index. Sometimes I work with like 22+ branches worksheets and it's a nightmare changing one by one. Thanks a bunch for your reply!
Hi there, thank you for making this video it really Helps. However I have some problem though. On the last part of the tutorial Filtering the year when I tried making some changes it gives me an error =filter(B2:C108,C2:C108>2010) it says: (Error
Array result was not expanded because it would overwrite data in D11. )But if I will change the 2010 to 2014 it works fine.
got it resolved. It seems like there's a space on D11 that's why. So just make sure if you have same problem remove any spaces on the column where you are filtering the data. :)
@@rockydeles1614 That must've been tricky; there seems to be nothing in there, so what am I overwriting?! But first, I would've tried this in different positions to see if it's the row 11 or D-column that has something off with it. If it works in E11, and it works in D12, then it must be cell D11 and you should try deleting it. Diagnostics can be tricky, but trying to isolate the issue by changing one variable at a time works in situations like this.
Hi Sir - I am challenging an error with my Google sheet. I am getting an error "You can't vertically merge cells that intersect an existing filter" I have already removed all filters from the sheet. But still unable to merge cells. Could you please help me?
Yes, Please help on this
Do you have any previously saved filter views?
No, I don't have any filter previously.
@@ExcelGoogleSheets No
Not sure. Try to duplicate the worksheet, and see if it works on the duplicate.
Query and filter can be similar. Correct?
Correct.
Please how to filter by partial text.
Thank you bro,,
I have a question, what if filter by date with references cell mergecell ?
A | B
1 2020/01/02 $20
2 $400
3 2020/01/04 $100
4 $32
5 $55
6 $32
7 2020/01/10 $100
What's in the merged cell?
@@ExcelGoogleSheets I want to filter to show $20 & $400 data based on the date 2020/01/02 which has mergecells A2&A2, can help solve it ?
Assuming the date you're searching for is in E2 cell, this should work =ArrayFormula(OFFSET($A$1,match(E2,A:A,0)-1,1,iferror(MATCH(TRUE,(INDEX(A:A, match(E2,A:A,0)+1):A10000)"",0),10000)))
@@ExcelGoogleSheets webapps.stackexchange.com/questions/148136/filter-by-merged-cells-in-google-sheets
How do you think you can solve this problem? I tried to apply it but failed
WHAT IF I WANT BACK COLUMN A AND C
This function cannot be found in normal Excel versions.
Yes, but it is coming. They have FILTER function available in their new preview.
I’m looking for na automatic sum script in Google Sheets... Similar to this in Excel VBa... If I can make a vídeo with something in this Direction.
Sub Autosoma()
Dim stgUltimalinha As String
Dim stgAutosoma As String
stgAutosoma = "A" & Range("A" & Rows.Count).End(xlUp).Row - 1
stgUltimalinha = "A" & Range("A" & Rows.Count).End(xlUp).Row
Range(stgUltimalinha).Value = WorksheetFunction.Sum(Range("A2:" & stgAutosoma))
End Sub
TAmil languages ley teach pannuinga pls pls pls
I wish he did date, or number, ranges.
25:00
U r video vision is not clear.
Every time you delete your formulas, it feels like you threw away a piece of art.
:)
+
Thank you very much!