Google Sheets - Filter Function Tutorial, Introduction to Logical Arrays

Поділитися
Вставка
  • Опубліковано 25 сер 2024

КОМЕНТАРІ • 125

  • @wademartinishere
    @wademartinishere 5 років тому +19

    UA-cam should have a "Love" button. Excellent video!

  • @user-gm9hu6tm7l
    @user-gm9hu6tm7l 3 роки тому +2

    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.

  • @aaronanttila7459
    @aaronanttila7459 4 роки тому +8

    All your videos are incredible. Phenomenal teacher!

  • @moheuddin_sehab
    @moheuddin_sehab 4 роки тому +5

    Such a amazing method. However you are more amazing. Your teaching method is phenomenal!

  • @claystudiofx
    @claystudiofx 3 роки тому +1

    everytime i watch every single video of you , everything looks so simple.

  • @sumayahghamdi5550
    @sumayahghamdi5550 4 місяці тому +1

    Your way of teaching is really smart.
    Thank you.

  • @ManojGuptahome
    @ManojGuptahome 2 роки тому +1

    excellent explanation of many hidden features of spreadsheet formulas. It makes my life easier and many things are doable now.

  • @StefanoVerugi
    @StefanoVerugi 3 роки тому +1

    Excellent work!
    You remind me of Sal Khan in his early days, outstanding teaching method
    So much appreciated

  • @Aztunxus
    @Aztunxus 4 дні тому

    Full of nice tips

  • @sugumarsit9313
    @sugumarsit9313 4 роки тому +2

    This video really helps me what I want. Thank You

  • @Carrymejane
    @Carrymejane 16 днів тому

    Best explanation ever

  • @akahatapodcast
    @akahatapodcast 3 роки тому +1

    You are a great teacher. Thanks for the video!

  • @XCelVietnam
    @XCelVietnam 3 роки тому +1

    Thank you a lot, the clip is long but very thoroughly shared

  • @robertmaluka2763
    @robertmaluka2763 5 років тому

    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

    • @tiktik5928
      @tiktik5928 2 роки тому

      Looking for the same....

  • @balamira297
    @balamira297 2 роки тому

    One of my favourite functions

  • @RomeoTudose
    @RomeoTudose Рік тому +3

    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

  • @yashavantpatel8771
    @yashavantpatel8771 3 роки тому +1

    Very very helpful 👍. Thank you so much 🥰.

  • @oneBhairavOnline
    @oneBhairavOnline 2 роки тому

    Saving this play list

  • @hoiyinwan8233
    @hoiyinwan8233 2 роки тому

    Thank you for your lesson! It is much appreciated!

  • @alansavage3549
    @alansavage3549 5 років тому

    Thanks. Going play around with it. My most common challenge is filtering/matching/extracting data from 2 or more sheets.

  • @enhancedcalm
    @enhancedcalm 4 роки тому

    Really good video. Working with a practical dataset helped make it clear.

  • @abhinavm8871
    @abhinavm8871 3 роки тому

    This tutorial are awesome, thank you bro, it helped me a lot, thanks again

  • @jamieheiney4290
    @jamieheiney4290 4 роки тому

    Thanks for including the practice file!

  • @kuysneil22
    @kuysneil22 4 роки тому

    straight to the point video! love it.

  • @davidgreen1051
    @davidgreen1051 Рік тому

    Very helpful - THANK YOU!!

  • @ihgaming8885
    @ihgaming8885 5 років тому +3

    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

  • @pushpaksharma6488
    @pushpaksharma6488 4 роки тому

    Thank you for sharing practice sheet, much needed

  • @KingCommerce
    @KingCommerce 4 роки тому +1

    Got the answer I was looking for in the first 19 Seconds. Briiliant, thanks :)

  • @ronelarinal1865
    @ronelarinal1865 3 роки тому

    thanku, this is what im looking at.

  • @leoengy
    @leoengy 5 років тому

    You are the best! Great video! It helped me a lot ! thank you , sir!

  • @paulloup5210
    @paulloup5210 5 років тому +1

    Thank you very much !!!

  • @ngoctan2512
    @ngoctan2512 4 роки тому

    Very useful video !! Thank so much

  • @KhalilYasser
    @KhalilYasser 5 років тому

    Thank you very much for this awesome video

  • @AsdrubalMaza
    @AsdrubalMaza 4 роки тому

    you are the best

  • @eljinet
    @eljinet 2 роки тому

    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

  • @NbaLive4ever
    @NbaLive4ever Рік тому

    Can the query formula do whatever the filter can do?

  • @natsrinuruk3983
    @natsrinuruk3983 2 роки тому

    What happens if the column I want to link over has a formula in it? My one comes up with the error #value

  • @moheuddin_sehab
    @moheuddin_sehab 5 років тому +1

    damn.. you are so good

  • @davidb5511
    @davidb5511 5 років тому +2

    Love the video thank you. How do you return specific columns in the filter function i.e columns A and C excluding B.

    • @ralphschraven339
      @ralphschraven339 3 роки тому +2

      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.

  • @tian2198
    @tian2198 Рік тому

    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.

  • @carlolee14solidad
    @carlolee14solidad 4 роки тому

    very nice.. love u

  • @yazmut
    @yazmut Рік тому

    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?

  • @rajafraz1003
    @rajafraz1003 2 роки тому

    how to take data range from multi sheets thank you

  • @gallowsmere3183
    @gallowsmere3183 5 років тому

    Really good thank you

  • @EIStudent
    @EIStudent 5 років тому

    This rocked!

  • @chocochiligaming1541
    @chocochiligaming1541 2 роки тому

    Hi, can you create a video about this filter + textjoin. your textjoin vid helped me alot at work. thanks

  • @jacobwoelk5831
    @jacobwoelk5831 2 роки тому

    Hey Is There Any Way To Use This In And Inportrange

  • @Bruno87198
    @Bruno87198 3 роки тому

    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

  • @AlexanderAbramovNN
    @AlexanderAbramovNN 2 роки тому

    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.

  • @dharma.vibrates
    @dharma.vibrates 3 роки тому

    Thank you Sir, how to use all this with another sheet ? Is it possible to search with on-click filter function ?

  • @TealMaria
    @TealMaria 5 років тому

    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!

  • @adeep598
    @adeep598 4 роки тому

    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

    • @googlesheetsph
      @googlesheetsph 3 роки тому +1

      You can use an array for range, ex: =FILTER({A:A,C:C},B:B="Handsome")

  • @BLA1NE
    @BLA1NE 2 роки тому

    개꿀팁 👍🏻

  • @Nevir202
    @Nevir202 2 роки тому

    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?

    • @ExcelGoogleSheets
      @ExcelGoogleSheets  2 роки тому

      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.

    • @Nevir202
      @Nevir202 2 роки тому

      ​@@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.

  • @Taufiqmusic
    @Taufiqmusic Рік тому

    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
      @ExcelGoogleSheets  Рік тому

      if(A1="",TRUE,condition)

    • @Taufiqmusic
      @Taufiqmusic Рік тому

      ​@@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

    • @ExcelGoogleSheets
      @ExcelGoogleSheets  Рік тому

      Filter(Range, if(A1="", MAP(B1:B5,LAMBDA(_,TRUE)), isnumber(MATCH(B1:B5, {A1},0)))

    • @Taufiqmusic
      @Taufiqmusic Рік тому

      @@ExcelGoogleSheets Heyy there! Followed exactly and this works! Thank you so much! :)

  • @WenSaiPanther
    @WenSaiPanther 4 роки тому +1

    I just wish the video was clearer so I could see the characters he was typing. Being blurry is an eye strain.

  • @lazalazarevic6192
    @lazalazarevic6192 5 років тому

    Blessed be!
    So for larger datasets is it better to use Filter or Query function?

    • @ExcelGoogleSheets
      @ExcelGoogleSheets  5 років тому +1

      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".

    • @wilsonfaustino3576
      @wilsonfaustino3576 5 років тому

      @@ExcelGoogleSheets Hi. Thanks a lot for your videos. What do you use for large data-sets?

    • @GeorgePlaten
      @GeorgePlaten 5 років тому

      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)

  • @keimarshal988
    @keimarshal988 5 років тому

    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

  • @GlazeLee12cU
    @GlazeLee12cU 5 років тому +1

    Can we develop an advanced search cell?

  • @irfanbashir1314
    @irfanbashir1314 5 років тому

    Please how to filter by partial text.

  • @bruno_veloso
    @bruno_veloso 3 роки тому

    Is there a way to use FILTER function with a custom array? Or in that case using QUERY would be more simple?

    • @ExcelGoogleSheets
      @ExcelGoogleSheets  3 роки тому

      What you mean by "custom array"?

    • @bruno_veloso
      @bruno_veloso 3 роки тому

      @@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
      @ExcelGoogleSheets  3 роки тому

      FILTER({Page1!A1:G10;Page2!A1:G10};{Page1!A1:A10;Page2!A1:A10} > 8)

    • @bruno_veloso
      @bruno_veloso 3 роки тому

      @@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!

  • @dianaaguilar7332
    @dianaaguilar7332 3 роки тому

    WHAT IF I WANT BACK COLUMN A AND C

  • @divinorodriguesdasilva5048
    @divinorodriguesdasilva5048 3 роки тому

    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

  • @rockydeles1614
    @rockydeles1614 4 роки тому

    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.

    • @rockydeles1614
      @rockydeles1614 4 роки тому +1

      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. :)

    • @ralphschraven339
      @ralphschraven339 3 роки тому

      @@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.

  • @Vivek-np9vm
    @Vivek-np9vm 4 роки тому

    Query and filter can be similar. Correct?

  • @bohdansaychuk8672
    @bohdansaychuk8672 3 роки тому

    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)?

    • @ExcelGoogleSheets
      @ExcelGoogleSheets  3 роки тому +1

      FILTER only filters by columns. What are you trying to accomplish?

    • @bohdansaychuk2877
      @bohdansaychuk2877 3 роки тому

      @@ExcelGoogleSheets Thanks for your reply. I mean something like that: docs.google.com/spreadsheets/d/1hoE3pOVmJak5Ia2dt8Iyj7sQ74Icss7B6PR0F2iNX5k/edit?usp=sharing

    • @ExcelGoogleSheets
      @ExcelGoogleSheets  3 роки тому +1

      Why not just use index/match?

    • @bohdansaychuk2877
      @bohdansaychuk2877 3 роки тому

      @@ExcelGoogleSheets Thank you! it works.

  • @philippecote4447
    @philippecote4447 5 років тому

    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
      @ExcelGoogleSheets  5 років тому

      The formula looks correct. You must be making a mistake matching your data to your condition.

    • @philippecote4447
      @philippecote4447 5 років тому

      @@ExcelGoogleSheets Thanks, I’ll have another look then.

  • @cuneiformscript2665
    @cuneiformscript2665 4 роки тому

    🙏🏻

  • @LotfyKozman
    @LotfyKozman 5 років тому

    This function cannot be found in normal Excel versions.

    • @ExcelGoogleSheets
      @ExcelGoogleSheets  5 років тому +1

      Yes, but it is coming. They have FILTER function available in their new preview.

  • @vishalmarwaha9996
    @vishalmarwaha9996 4 роки тому

    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?

  • @Chanderv31
    @Chanderv31 7 місяців тому

    25:00

  • @eurigroupoffice3027
    @eurigroupoffice3027 3 роки тому

    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

    • @ExcelGoogleSheets
      @ExcelGoogleSheets  3 роки тому

      What's in the merged cell?

    • @eurigroupoffice3027
      @eurigroupoffice3027 3 роки тому

      @@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 ?

    • @ExcelGoogleSheets
      @ExcelGoogleSheets  3 роки тому

      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)))

    • @eurigroupoffice3027
      @eurigroupoffice3027 3 роки тому

      @@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

  • @WenSaiPanther
    @WenSaiPanther 4 роки тому

    I wish he did date, or number, ranges.

  • @udhayasankar2459
    @udhayasankar2459 4 роки тому

    TAmil languages ley teach pannuinga pls pls pls

  • @singhalmonica
    @singhalmonica 4 роки тому

    U r video vision is not clear.

  • @ArmadusMalaysia
    @ArmadusMalaysia 2 роки тому

    Every time you delete your formulas, it feels like you threw away a piece of art.

  • @chrisklehm717
    @chrisklehm717 4 роки тому

    +

  • @veresku
    @veresku 5 років тому

    Thank you very much!