+ Reply to Thread
Results 1 to 20 of 20

Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

  1. #1
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10

    Question Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Hi,

    Is there a formula to find out all the possible fuzzy/partial matches for a single value (there is only one column in the sheet)?

    Column A has all the data (there are some duplicate values which can be found out using conditional formatting). Apart from that there are many values like Samsung, Samsung Inc, Samsung Corp, The Samsung, LG, The LG, LG Inc., Microsoft, Microsoft Inc., Microsoft Co., The Microsoft etc. from cell A1 to cell A25000

    I am using this formula: =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$25000)/(ISNUMBER(SEARCH($B$1,$A$1:$A$25000))),ROW())),""), but it gives all the matches only for cell A1, i have to manually change the cell reference every time to get the other partial matches. I want to avoid this manual part.

    Note: Fuzzy Lookup add in, power query and vba cannot be used

    Thanks in advance
    Attached Files Attached Files
    Last edited by ashish002; 07-09-2021 at 02:14 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    51,216

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here I will do it for you today.

    https://chandoo.org/forum/threads/fu...and-vba.46571/

    https://www.mrexcel.com/board/thread...d-vba.1175606/)
    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.

  3. #3
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Apologies for missing out on that, the reason was since that formula worked only for one cell, i had to change the cell reference every time which is very time consuming for a big data set.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    51,216

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    That's the strangest explanation for cross-posting that I've ever read!!!

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,955

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Can you please post a sample w/book. See yellow banner at top of thread.

    you appear to have an answer on the other threads - correct or not ?
    Attached Files Attached Files
    Last edited by JohnTopley; 07-08-2021 at 03:37 PM.

  6. #6
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Posted a sample workbook.

    I am using this formula: =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$25000)/(ISNUMBER(SEARCH($B$1,$A$1:$A$25000))),ROW())),""), but it gives all the matches only for cell A1, i have to manually change the cell reference every time to get the other partial matches. I want to avoid this manual part.

    Something is preventing it from showing results for other cells.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Your attempts to explain this are a) incorrect and b) make no sense.

    1. It is searching the value in B1 (not A1)

    2. What do tyou REALLY expect the fiormula to do.... dream up a search temr and then search for it?? YOU have to tell it what to search for. That is what it is doing . It is looking in B1.

    You need to CLEARLY define what you want... It looks to me like you are wanting the formula to search for unicorns.
    Glenn



  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,955

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    The same formula in post #5 works: did you try it?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    I have broken down the words in column A, for eg: if it is 'samsung inc. in cell A1' after breaking down the word, it will show samsung in b1 and inc. in c1

    Formula picks up the words from column B and searches in column A for all the other words that contain word in B1

    I need the formula to shift to b2 (instead of manually referencing it) and continue its search in column a, once it completes the search for b1

    Let me know if you understood my point

  10. #10
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Yes, I tried that, We have to manually change the reference or pick the unique words from column A and then run the formula

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,955

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Something like the attached:

    I removed duplicates from column B (this could be done by formula)

    in F1 and copied across

    =INDEX($B$1:$B$20,COLUMNS($A:A))

    in F2 and copied across

    =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$1:$A$9999)/(ISNUMBER(SEARCH(F$1,$A$1:$A$9999))),ROWS($1:1))),"")
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Thanks John,

    I will confirm once i apply this to actual data set.

  13. #13
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Quote Originally Posted by JohnTopley View Post
    Something like the attached:

    I removed duplicates from column B (this could be done by formula)

    in F1 and copied across

    =INDEX($B$1:$B$20,COLUMNS($A:A))

    in F2 and copied across

    =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$1:$A$9999)/(ISNUMBER(SEARCH(F$1,$A$1:$A$9999))),ROWS($1:1))),"")
    Hi John,

    I applied this formulas to actual data set. It works like a charm.

    Since excel has limitation of 16000 columns, i could apply it only till 16000th column.

    It's too much to ask, the results which gets displayed column wise for each word, can we put it all in one column?

    Thanks again for your time and efforts.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,955

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    You had more than 16000 unique values?

  15. #15
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10
    Quote Originally Posted by JohnTopley View Post
    You had more than 16000 unique values?
    Yes, 23000

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,955

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Out of 25000 rows ? I suspect many are redundant (like "the" ,"Co","Inc"

    Post your file (ZIP if required)

  17. #17
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    Quote Originally Posted by JohnTopley View Post
    Out of 25000 rows ? I suspect many are redundant (like "the" ,"Co","Inc"

    Post your file (ZIP if required)
    Hey John,

    Apologies, I was unwell so couldn't revert back.

    This problem is sorted now. I have recently got a new data wherein the formula fails to capture value if there is '.' in between 2 words or if there is difference of '&' and 'and'

    I have attached sample file for your reference.

    The data is huge like close to 10,000 rows, can we force that formula to capture this kind of values also or is there any other way
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,955

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    There is no easy approach to this: the attached helps with the "." and "and" problem

    in B2

    =substitute(substitute(A2,".","")," and ","&")

    in C2

    =IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$1:$A$9999)/(ISNUMBER(SEARCH("*" & C$1&"*",$B$1:$B$9999))),ROWS($1:1))),"")

    Copy across

    The other issue is allocating the "search" parameters in C1 onwards

    Given the list of 10,000 (column A) values I would be prepared to see if these can be "filtered" into a reasonable set using VBA.
    Attached Files Attached Files
    Last edited by JohnTopley; 07-22-2021 at 01:36 PM.

  19. #19
    Registered User
    Join Date
    07-08-2021
    Location
    India
    MS-Off Ver
    2016
    Posts
    10
    Quote Originally Posted by JohnTopley View Post
    There is no easy approach to this: the attached helps with the "." and "and" problem

    in B2

    =substitute(substitute(A2,".","")," and ","&")

    in C2

    =IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$1:$A$9999)/(ISNUMBER(SEARCH("*" & C$1&"*",$B$1:$B$9999))),ROWS($1:1))),"")

    Copy across

    The other issue is allocating the "search" parameters in C1 onwards

    Given the list of 10,000 (column A) values I would be prepared to see if these can be "filtered" into a reasonable set using VBA.
    The formulas which you mentioned solved the issues

    Thanks again for your time and efforts.

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,955

    Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

    You're welcome.

    If you are happy with the solution provided please mark as solved by clicking on "Thread Tools" at top of thread.

+ 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. [SOLVED] Fuzzy Lookup - More than 1 match
    By Un-Do Re-Do in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-03-2020, 10:59 PM
  2. Fuzzy Vlookup Help (Can't get longer strings to match simplified lookup)
    By rosetc16 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-10-2020, 11:54 AM
  3. Replies: 1
    Last Post: 08-24-2015, 10:42 AM
  4. fuzzy lookup add on, specifying what to match against
    By IronCladRooster in forum Excel General
    Replies: 0
    Last Post: 08-14-2014, 10:06 AM
  5. [SOLVED] Combobox find part matches from defined range (POWER FUZZY SEARCH)
    By hutzdani in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2013, 05:31 AM
  6. Fuzzy about fuzzy formula code
    By dmanatee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2010, 04:23 PM
  7. Cell search fuzzy word match lookup
    By datacruncher in forum Excel General
    Replies: 1
    Last Post: 12-03-2009, 07:05 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1