+ Reply to Thread
Results 1 to 13 of 13

Find if string is a part from lookup list

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

    Find if string is a part from lookup list

    Hi Guys,

    i have a list like here:

    Screenshot_41.png

    and have also table with part of strings from the list:

    ImageNames
    thumbnail
    smallImage
    coverImage
    images

    What i want to do is loop from "List" worksheet and check if part of string matches with SourceTable lookup value.
    In example all string matches.

    So "docs » coverImage" are like "coverImage" from SourceTable. so it matches.

    How can i do this? What method to use to do this in optimal way?

    Best,
    Jacek
    Attached Files Attached Files

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Find if string is a part from lookup list

    I do not know why can't edited post.
    Sorry I did not spot that macros expected.
    Attached Files Attached Files
    Last edited by KOKOSEK; 07-21-2020 at 05:48 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Find if string is a part from lookup list

    Hi

    Another way is to use a custom function..
    Please Login or Register  to view this content.
    see attached example

    zeddy
    son-of-Pole, in Newcastle
    Last edited by zzzeddy; 07-21-2020 at 05:47 AM.

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

    Re: Find if string is a part from lookup list

    thank you Guys,

    nice solutions. But i need only VBA solution. zzzeddy your function is looping each time in the list for each cell.
    In case of hundreds of variables it will be slow.

    Maybe i could use function what Kokosek wrote in VBA...

    Jacek

  5. #5
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Find if string is a part from lookup list

    No, I've created formula in 1st post.
    Maybe if we grab checktable into array in VBA and then check values --> output results should be faster.
    Last edited by KOKOSEK; 07-21-2020 at 06:00 AM.

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

    Re: Find if string is a part from lookup list

    Kokosek you mean using loop?

    It is better to find match not to loop i think ?

    I know that you used formula and this is very nice! Fast and i am wondering it would be nice to use evaluate and use this formula to find match in VBA directly.

    Jacek

  7. #7
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Find if string is a part from lookup list

    Manual run of macro to check your list.
    Inside macro: grab list into array they loop through cell to check to check based on array is it anything matches and displa result in particular cell next to (or something else, depends what you need).
    I'll take a look

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

    Re: Find if string is a part from lookup list

    KOKOSEK maybe i could use filter to match, not to loop?

    Jacek

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

    Re: Find if string is a part from lookup list

    i was thinking to use something like:

    WorksheetFunction.CountIf(range, lookupValue) but i am getting error,
    can anybody help?

    Best,
    Jacek

  10. #10
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Find if string is a part from lookup list

    that's gives you a loop anyway (loop from list as lookupValue)
    Do you want to UDF (zeddy's function looks fine) or can be just macro to run manualy?
    Last edited by KOKOSEK; 07-21-2020 at 10:03 AM.

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

    Re: Find if string is a part from lookup list

    Just a macro.

    Once time jindon wrote awesome code:

    Please Login or Register  to view this content.
    and maybe this i could use?
    But i have problem with adjusting it.

    Jacek

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

    Re: Find if string is a part from lookup list

    Ah i still having problem with implementing this.

    i have just used:

    Please Login or Register  to view this content.
    but this is slow.

    The best way would be to use filter method like jindon used but i having issues with implementig it.

    I am trying to use filter method because seems very fast combined with evaluate function.

    So in the loop in VBA i have to check every cell: A1, A2, A3 and so on if filter is applied.

    Please Login or Register  to view this content.
    this return 0.why?
    Can anybody help ?

    Best,
    Jacek
    Attached Images Attached Images
    Attached Files Attached Files

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

    Re: Find if string is a part from lookup list

    Hi,

    i wrote nice function like here:

    Please Login or Register  to view this content.
    Which is working but what i want to get number as result - so if any value matches - so in this example - yes (we have one "1" and rest is FALSE) , we have one match - so i know that i can sue this value. Or filter the created array in order to know if variable from A1 is a part of variables from SourceTable.
    Filtering array seems to be very fast way like i have here in example:

    https://www.excelforum.com/excel-pro...ml#post5336787

    I checked on 100 hundreds rows and working like a charm.
    In other hand my function with evaluate IfCount from previous post is very slow.
    So filter with evaluate seems to be very nice solution but how to implement and why i am getting 0 ?

    Screenshot_13.png

    How can i do this?

    I am attaching newest example.

    Best,
    Jacek
    Attached Files Attached Files
    Last edited by jaryszek; 07-23-2020 at 03:31 AM.

+ 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. Lookup to reference part of a string of text.
    By Cleric82 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2017, 10:34 AM
  2. [SOLVED] Lookup formula to find part of a text string
    By A440 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-30-2015, 08:21 AM
  3. [SOLVED] How Do I Find and Return a String When Only Part of that String is Known?
    By Foamy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-08-2014, 05:31 AM
  4. [SOLVED] Find a string by VBA Regular Expression and replace a part of that string
    By taps in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-25-2013, 11:08 AM
  5. [SOLVED] LOOKUP using only part of a string?
    By KiwiLad in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-08-2012, 01:26 AM
  6. [SOLVED] Help with a lookup that looks for part of a string
    By Ainsley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-02-2012, 06:02 AM
  7. Replies: 8
    Last Post: 12-08-2008, 12:54 PM

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