+ Reply to Thread
Results 1 to 7 of 7

How can I use FILTER to show results where any column contains today's date?

  1. 10-28-2021, 12:53 AM #1
    jmdenson
    jmdenson is offline
    Registered User
    Join Date
    10-28-2021
    Location
    Texas, USA
    MS-Off Ver
    Office 365
    Posts
    4

    How can I use FILTER to show results where any column contains today's date?

    Hi all!

    I'm trying to automate a daily task that we have in my department using the FILTER function, but I'm hitting some snags.

    Using the attached example, what I need to do is, on Sheet2, return a list of values from column A (SERIALNUM) where any of the "Open Date" columns (C, E, G, and I) have the current date (today is 10/27/21) in the corresponding row.

    So, ideally, from the attached example, I would end up with a list showing the following two serial numbers:

    DEF2
    GHI3

    The actual document is much larger with many more columns and rows, but hopefully you get the idea.

    I've tried using the following formula:

    Please Login or Register  to view this content.
    where B1 is the current date with =TODAY() but all this gets me is a #VALUE error. I've been at this for hours and I'm about at the limit of my excel formula knowledge. Any help is appreciated.
    Attached Files Attached Files
    Last edited by jmdenson; 10-28-2021 at 07:31 PM. Reason: clarifying title
    Reply With QuoteRegister To Reply
  2. 10-28-2021, 01:09 AM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How can I use FILTER to show results where any column contains today's date?

    This will work for the sample data but is obviously not ideal, especially as you mention you have more columns in the real data.

    =FILTER(Sheet1!A2:A6,(Sheet1!C2:C6=B1)+(Sheet1!E2:E6=B1)+(Sheet1!G2:G6=B1)+(Sheet1!I2:I6=B1),"No Results")
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 10-28-2021, 01:57 AM #3
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

    Re: How can I use FILTER to show results where any column contains today's date?

    Sheet2

    A2=IFERROR(INDEX(Sheet1!$A$2:$A$100,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1/(Sheet1!$B$2:$Z$100=Sheet2!$B$1)/(Sheet1!$B$1:$Z$1=Sheet1!$C$1),ROWS(Sheet1!$A$2:A2))),"")

    Copy down
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-28-2021, 02:50 AM #4
    jmdenson
    jmdenson is offline
    Registered User
    Join Date
    10-28-2021
    Location
    Texas, USA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How can I use FILTER to show results where any column contains today's date?

    Quote Originally Posted by Norie View Post
    This will work for the sample data but is obviously not ideal, especially as you mention you have more columns in the real data.

    =FILTER(Sheet1!A2:A6,(Sheet1!C2:C6=B1)+(Sheet1!E2:E6=B1)+(Sheet1!G2:G6=B1)+(Sheet1!I2:I6=B1),"No Results")
    This does work, although it's a bit cumbersome - I'd like something a little more dynamic but this will do if it comes down to it!
    Reply With QuoteRegister To Reply
  5. 10-28-2021, 03:29 AM #5
    jmdenson
    jmdenson is offline
    Registered User
    Join Date
    10-28-2021
    Location
    Texas, USA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How can I use FILTER to show results where any column contains today's date?

    Quote Originally Posted by CARACALLA View Post
    Sheet2

    A2=IFERROR(INDEX(Sheet1!$A$2:$A$100,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1/(Sheet1!$B$2:$Z$100=Sheet2!$B$1)/(Sheet1!$B$1:$Z$1=Sheet1!$C$1),ROWS(Sheet1!$A$2:A2))),"")

    Copy down
    So this didn't work for me, but I think I know why. I inadvertently left out that the dates in the sheet are actually themselves determined by formulas. I'm sure that makes a difference but I have no clue how to get around it.

    I suspect that this might be more complex than I made it out to be, so I'm including a sort-of stripped down version of the actual data so you can see what I'm working with. As I write this the date is now 10/28, so the only one that should be on the list for today is "MCO-CD" from A9.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-28-2021, 04:51 AM #6
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

    Re: How can I use FILTER to show results where any column contains today's date?

    OPEN TODAY

    A2=IFERROR(INDEX('New Cadence July'!$A$2:$A$100,AGGREGATE(15,6,ROW('New Cadence July'!$A$2:$A$100)-ROW('New Cadence July'!$A$2)+1/('New Cadence July'!$D$2:$AU$100='OPEN TODAY'!$B$1)/('New Cadence July'!$D$1:$AU$1='New Cadence July'!$E$1),ROWS('New Cadence July'!$A$2:'New Cadence July'!A2))),"")

    Copy down
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-28-2021, 07:31 PM #7
    jmdenson
    jmdenson is offline
    Registered User
    Join Date
    10-28-2021
    Location
    Texas, USA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: How can I use FILTER to show results where any column contains today's date?

    Quote Originally Posted by CARACALLA View Post
    OPEN TODAY

    A2=IFERROR(INDEX('New Cadence July'!$A$2:$A$100,AGGREGATE(15,6,ROW('New Cadence July'!$A$2:$A$100)-ROW('New Cadence July'!$A$2)+1/('New Cadence July'!$D$2:$AU$100='OPEN TODAY'!$B$1)/('New Cadence July'!$D$1:$AU$1='New Cadence July'!$E$1),ROWS('New Cadence July'!$A$2:'New Cadence July'!A2))),"")

    Copy down
    Worked perfectly this time, thank you!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Advanced Data FIlter: Two columns: Numbers as text, Characters as text: Results different
    By brucemc777 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-17-2018, 10:20 AM
  2. [SOLVED] VBA filter to show next day results
    By minakiel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-08-2015, 09:15 AM
  3. Show column header values vertically vs. horizontally in pivot results
    By Scott1807 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-29-2015, 04:39 AM
  4. [SOLVED] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  5. SHow filter in a header.
    By Lensmeister in forum Excel General
    Replies: 1
    Last Post: 12-02-2013, 12:14 PM
  6. How to copy a range of columns from sheet1 to sheet2 if the cell show a specific text
    By chermaine123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2011, 02:39 AM
  7. How do I show the results of filter?
    By [email protected].(donotspam) in forum Excel General
    Replies: 1
    Last Post: 08-02-2005, 05:05 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1