+ Reply to Thread
Results 1 to 10 of 10

Equivalant to Google Filter Function

  1. #1
    Registered User
    Join Date
    07-10-2019
    Location
    Missouri, US
    MS-Off Ver
    O365
    Posts
    12

    Equivalant to Google Filter Function

    I have data concerning classes and dates, times, etc. I need to be able to use a FILTER function in Excel, similar to Google's FILTER but there is not one available. MS says they have had it in BETA for over a year, however no plans on a release date, or if it will be released.

    The attached sample shows a very simplified version fo the data I need to filter and the example of what it would need to look like on the end result. I originally was thinking I could use a VLOOKUP command and return data based on TODAY() matching today's date, however this only returns the first entry and there will be multiple entries with the same date. I need all of the entries returned that match today's date. I am looking for some kind of function or formula that can do this automatically, so that after midnight the sheet will update to the data that matches the next day. The end result will be published to a Sharepoint site so I need it to filter and update so that visitors to the site can see the schedule as it pertains to today's date without having to do anything other than just load the sharepoint page. I wsa using GOOGLE Sheets to complete this in the past, however due to increased security requirements our company is switching from Google to MS products. Below is an example of the command that worked in Google sheets:

    =FILTER(A2:D,A2:A=TODAY())

    A very simple command that filters out and returns only items in the table that match today's date in Column A.

    I have an extreme dislike of Pivot Tables and absolutely no knowledge of how they work at all, which is mainly why I don't like them, but from what I have seen they also will not format the table into rows and columns like I want. I found several references to try and use something called Power Query, however I am using O365 on a MAC and it seems to not be available. It keeps saying to go to the DATA tab and select get external data from a Excel sheet, however I do not have an option to get external data from an excel sheet or other sources, only from HTML, Text or New Database Query and no options in the settings to add other sources or anything else to the ribbon so it may be restricted by our IT department, or else it is just not there.

    I definitely appreciate any help, if this is even possible. This forum has been a great lifesaver and time saver over the past few days in finding and getting answers on how to do things during this conversion from Google to Excel.
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    14,603

    Re: Equivalant to Google Filter Function

    How is this question different from Comparable FILTER function in google sheets, but need it to work in Excel instead?
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Valued Forum Contributor
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    1,070

    Re: Equivalant to Google Filter Function

    maybe this as an array formula

    Please Login or Register  to view this content.
    confirm with ctrl shift enter
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  4. #4
    Valued Forum Contributor
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    1,070

    Re: Equivalant to Google Filter Function

    edit - probably you should look at the question on this forum that 6 string Jazzer mentions as I'd probably adapt 63falcondude's solution.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    28,111

    Re: Equivalant to Google Filter Function

    The other query is significantly different.

    Here's a PowerQuery solution.

    I used this M code:

    Please Login or Register  to view this content.
    which produces this:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    23
    Date Name Class Time
    24
    12/07/2019
    John
    7
    9AM-5PM
    25
    12/07/2019
    Bob
    8
    10AM-6PM
    Sheet: Sheet1

    You just need to manually refresh the query each day or set the query to refresh automatically.

    There is a link in my signature line for information about how to use PQ code - just shout if you need further instructions.
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    28,111

    Re: Equivalant to Google Filter Function

    Oops - just realised you are using a Mac so the PQ solution will not work - sorry about that. I'll leave it here in case it is of any use to anyone else, though.

  7. #7
    Registered User
    Join Date
    07-10-2019
    Location
    Missouri, US
    MS-Off Ver
    O365
    Posts
    12

    Re: Equivalant to Google Filter Function

    When I click on your link, it says that the site cannot be reached.

  8. #8
    Registered User
    Join Date
    07-10-2019
    Location
    Missouri, US
    MS-Off Ver
    O365
    Posts
    12

    Re: Equivalant to Google Filter Function

    Quote Originally Posted by scottiex View Post
    maybe this as an array formula

    Please Login or Register  to view this content.
    confirm with ctrl shift enter
    This only returns the date and not the remainder of the columns, it also only returns the first instance and not multiple rows/columns.

  9. #9
    Registered User
    Join Date
    07-10-2019
    Location
    Missouri, US
    MS-Off Ver
    O365
    Posts
    12

    Re: Equivalant to Google Filter Function

    Quote Originally Posted by kevinldrbck View Post
    This only returns the date and not the remainder of the columns, it also only returns the first instance and not multiple rows/columns.
    With a slight modification, I was able to get the desired results with the following formula.

    =IFERROR(INDEX(A:A,SMALL(IF($A$2:$A$99999=TODAY(),ROW($A$2:$A$99999)),ROWS($A$2:A2))),"")

    Entering as an array (SHIFT+CONTROL+ENTER)

    Now my next step in the process is getting it to work on a different tab. I have taken the same formula and entered it on a different tab than where the data is at. I changed the cell references to reflect the tab name in the data ranges, however now I do nto get any results. The further modified formula I used s as follows:
    =iferror(index('Sheet1'!A:A,small(if('Sheet1'!$A$2:$A$99999=TODAY(),row('Sheet1'!$A$2:$A$99999)),ROWS'Sheet1'!$A$2:A2))),"")

    Which returns the results to another sheet. It looks like the final COLUMN command is what was causing only the date to be returned no matter where I placed the data. Thank you for your assistance, this will help out a lot in several different areas of this conversion process.

  10. #10
    Valued Forum Contributor
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 2010
    Posts
    1,070

    Re: Equivalant to Google Filter Function

    Woops...
    I meant

    Please Login or Register  to view this content.
    and if on another sheet

    Please Login or Register  to view this content.
    seems to work for me.
    (columns and rows parts are just counting the columns and rows so you can fill across and down easily.)

    using yours

    Please Login or Register  to view this content.
    Last edited by scottiex; 07-15-2019 at 12:22 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1