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

1. ## 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

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

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.

3. ## 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. ## 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. ## 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 ?

6. ## 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. ## 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.

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

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

9. ## 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. ## 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. ## 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))),"")

12. ## 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. ## Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

Originally Posted by JohnTopley
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. ## Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

You had more than 16000 unique values?

15. Originally Posted by JohnTopley
You had more than 16000 unique values?
Yes, 23000

16. ## 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. ## Re: Fuzzy Match Without Fuzzy Lookup Add in, Power Query and VBA

Originally Posted by JohnTopley
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

18. ## 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.

19. Originally Posted by JohnTopley
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. ## 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.

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