+ Reply to Thread
Results 1 to 2 of 2

Compare 2 partial strings against each other within FILTER function?

  1. #1
    Registered User
    Join Date
    09-15-2022
    Location
    Nashua, NH
    MS-Off Ver
    365
    Posts
    3

    Compare 2 partial strings against each other within FILTER function?

    Hello all, trying to use the new UNIQUE and FILTER function's.

    =UNIQUE(array,[by_col],[exactly_once])

    =FILTER(array,include,[if_empty]


    with the include function comparing a partial STRING to another partial STRING. I will be making 2 versions of this hopefully, one if STRINGS match, one if they do not.

    -The STRING is actually just a date, unfortunately on the array side, it is a date and time stamp that cell formatting cannot seem to grapple.


    Here are the important values:

    Date to compare against - CurrentMonth!AG2 i.e. 9/15/2022

    Array of dates within data - OldBackLog!I2:OldBackLog!I5000 i.e. 9/15/2022 4:22 PM

    Array of data to be filtered with only unique results- OldBackLog!A2:OldBackLog!J5000


    Here is the formula I have that I believe is correct:

    =UNIQUE(FILTER(OldBackLog!A2:OldBackLog!J5000, <INSERT COMPARISON HERE> ,""),FALSE,TRUE)


    And here are the 3 different attempts at a comparison I have tried so far:

    XMATCH(CurrentMonth!AG2?,OldBackLog!I2:OldBackLog!I5000,2,1)

    ISNUMBER(SEARCH(CurrentMonth!AG2,(OldBackLog!I2:OldBackLog!I5000)))

    LEFT(OldBackLog!I2:OldBackLog!I5000,5)=LEFT(CurrentMonth!AG2,5)

    Any help/advice will be greatly appreciated!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,492

    Re: Compare 2 partial strings against each other within FILTER function?

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Partial spilling of data by new Filter function
    By paradise2sr in forum Excel General
    Replies: 5
    Last Post: 11-08-2021, 04:56 AM
  2. [SOLVED] Transform User Defined Function to Sub (Filter Partial Concatenated Match) SOS
    By therealdees in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-10-2021, 12:14 PM
  3. [SOLVED] Advance filter or filter to get partial strings
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-22-2020, 01:33 PM
  4. [SOLVED] Compare a list of strings against a list of keywords to return a partial match
    By randomkiwi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-11-2017, 11:51 PM
  5. Replies: 4
    Last Post: 05-30-2015, 11:45 AM
  6. Looking for a function whitch would filter just capital strings from a cell.
    By Alan248 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-29-2014, 01:36 AM

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