+ Reply to Thread
Results 1 to 15 of 15

Advance filter or filter to get partial strings

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,461

    Advance filter or filter to get partial strings

    Hi,

    i am using code like here:

    Please Login or Register  to view this content.
    to set up rngToFilter range in one workbook.

    Also i i am getting array for criteria to use on second workbook.

    In second workbook i have:

    Please Login or Register  to view this content.
    this is working but only when i have eact match in array.
    But goal is to have partial match here.

    I tried with "*" & v & "*" but i am gettin error (typeMismatch).

    Why? How to filter it?
    Best,
    Jacek

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,260

    Re: Advance filter or filter to get partial strings

    And sample file is where?
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,461

    Re: Advance filter or filter to get partial strings

    Here you are,

    sorry for delay

    Code for sample:

    Please Login or Register  to view this content.
    If i will use "*" & v & "*" (partial string what is a goal here) i will get error.

    Jacek
    Attached Files Attached Files

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,260

    Re: Advance filter or filter to get partial strings

    What are you wanting to achieve...

    Store the values from Sheet1 Filter i.e. 1.2.3.4 and Filter col A Sheet 2 on wildcards of these values

  5. #5
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,461

    Re: Advance filter or filter to get partial strings

    hi,

    exactly this is what i want to achive here.

    I am storing values from sheet1, column1 with criteria "Any" to array and trying to use this array to filter another worksheet (for customer project - in another workbook) based on partial string.
    Please help how to do this.

    Jacek
    Last edited by jaryszek; 05-22-2020 at 05:51 AM.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,094

    Re: Advance filter or filter to get partial strings

    You can not use wild card with an array in autofilter and also can not filter different columns in one line.

    This uses countif formula so if the data is large, it will take time.
    Please Login or Register  to view this content.
    Last edited by jindon; 05-22-2020 at 06:25 AM.

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,461
    Thank you jindon. It seems complicated. What if I would have only one column to filter without color criteria? It should work ? I tried this and it didn't work for me maybe I am doing something wrong

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,094

    Re: Advance filter or filter to get partial strings

    1)
    Please Login or Register  to view this content.
    2) If no need to filter by color, AdvancedFilter can be also applied.
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,461

    Re: Advance filter or filter to get partial strings

    Thank you!!

    It is working like a charm

    Jacek

  10. #10
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,461

    Re: Advance filter or filter to get partial strings

    One more thing to ask,

    how can i now set the range from filtered one?

    Jacek

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,094

    Re: Advance filter or filter to get partial strings

    AdvancedFilter, you can adjust it to Aurofilter.
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,461

    Re: Advance filter or filter to get partial strings

    Thank you so much,

    Jacek

  13. #13
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,461

    Re: Advance filter or filter to get partial strings

    Please explain how this is working:

    "=sum(countif(a1,{" & x & "}))>0"

    In my real data i have result as FALSE but still this is working.
    For each variable is checking if there is occurence and it is - advance filter is taking it?

    Jacek

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,094

    Re: Advance filter or filter to get partial strings

    The result of the formula is just for the first record.
    AdvencedFilter will apply that condition to each row.

  15. #15
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,461

    Re: Advance filter or filter to get partial strings

    Great !! Thank you

+ 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. Help: Advance Filter
    By Shermaine2010 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-04-2016, 11:38 AM
  2. [SOLVED] Cant filter 2 columns even with advance filter
    By pingpong1 in forum Excel General
    Replies: 19
    Last Post: 07-03-2015, 04:40 PM
  3. Replies: 3
    Last Post: 09-07-2009, 06:28 AM
  4. Advance Filter
    By tt388 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-28-2008, 07:32 AM
  5. Filter Partial string, send to "Filter Sheet"
    By davemcochrane in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-13-2008, 11:28 AM
  6. Advance Filter
    By Obi-Wan Kenobi in forum Excel General
    Replies: 3
    Last Post: 03-22-2006, 05:50 PM
  7. Advance filter search does not filter an exact match
    By cfiiland in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2005, 08:05 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