Match a data entry sentence with the highest number of common words within a database

Hello Excelforum,

Once again I would really appreciate any insight at all into a problem and I am still learning to do things in excel from scratch. I have tried attempting things on my own using wildcards, keywords in formulas but haven't found a suitable solution yet.

I am looking for a function that can recognise each word in a data entry and compare it with the number of correct matches in each description in the database. The highest number of word matches get selected. If within the data-base, all the database entries have the same number of word matches, choose the highest percentage match. The data may need to be normalised as the database contains "," or "/" instead of spaces between words.

e.g.
user entry:
AIR CLEANER FILTER

database contains:
AIR CLEANER, PARTICULATE
AIR CLEANER, PARTICULATE PRECIPITATION
AIR CLEANER PARTICULATE ELECTROSTATIC PRECIPITATION/MOBILE
AIR CLEANER PARTICULATE HIGH-EFFICIENCY FILTER

Desired:
AIR CLEANER PARTICULATE HIGH-EFFICIENCY FILTER

user entry:
AIR CLEANER

database contains:
AIR CLEANER, PARTICULATE
AIR CLEANER, PARTICULATE PRECIPITATION
AIR CLEANER PARTICULATE ELECTROSTATIC PRECIPITATION/MOBILE
AIR CLEANER PARTICULATE HIGH-EFFICIENCY FILTER

Desired:
AIR CLEANER, PARTICULATE (highest percentage match)

A similiar idea was posted here:
http://www.excelforum.com/excel-gene...ercentage.html

with a custom function

Please Login or Register  to view this content.

I have attached some sample data if it helps.
Thank you very much. I appreciate any help whatsoever.

Jason

Hi

I think this is a bit of a fluke based on the data example, and the particular sorting that it has, but may get you started.

Please Login or Register  to view this content.

rylo

Originally Posted by rylo

Hi

I think this is a bit of a fluke based on the data example, and the particular sorting that it has, but may get you started.

Please Login or Register  to view this content.

rylo

Thanks rylo this seems to work quite well and i can easily use this within my other sheets. really nice.
im not quite sure how it works exactly. as in choosing the option. it works for any characters joining words which is nice. I'd love it if you could explain the 2 loops for me.

---------- Post added at 07:45 AM ---------- Previous post was at 07:43 AM ----------

Originally Posted by jindon

See if this helps

thank you jindon, yes it works. any chance of getting the password so i could have a look at the code?

i'd just like to say, jindon and rylo, thank you for helping me. i pretty much have what i am looking for.

Hi

I've put some comments into the code below.

Initially I was just chasing the max number of matches, then realised that it was working for the percentages as well. This seems to be because of the sorting so that the first items that it encounters for the most matches, will also have the highest percentage.

Please Login or Register  to view this content.

If you still have any questions, don't hesitate to come back with them.

rylo

rylo,

Im pretty sure i understand everything now. fantastic. is it possible to change it into a function where you can call the function entry by entry instead of running the macro and redo-ing every entry again.

either way command buttons are okay.

Thanks,

Jason

Hi

Here goes

Please Login or Register  to view this content.

This would be entered in C2 as =myfunc(A2,$B$2:$B$8)

rylo

Hello rylo,

Fantastic. Can I ask one final question?

This is probably my misunderstanding (well it is). Can i ask what the "ce" is in the code? Does it stand for column entry? Is that inbuild into excel? Cant see the definition

Thanks again for everything

Hi

ce is just a variable. It could have been i, or j etc. I use ce as a personal preference thing that has just become a habit. I mostly use it when I'm going though a range with a for loop. It is really short for cell, but as cell is a key work, then I don't use it. Think of it as for each cell in the range.....

That's all.

rylo

I see, so you do not need to explicity define variables.

Is there any way to avoid the bug of if you have a single word, it matches the first entry in the database only? The bug occurs in both yours and jindon's code, somehow.

I have attached a sample sheet to describe it.

Also, is this the correct way of using a database on a different worksheet? Shown below. I have attached a prefix Sheets("Sheet2"). but cant seem to get it to work.

Please Login or Register  to view this content.

I hope other people like me can pick up from this as well, rylo. Thank you so much,

Jason

Jason

I've worked with the function on this. Updated it to put in a blank instead of the 0.

Try sorting the data in column B in ascending order. That seems to bring back the results you want. Go back to post #2 where I did comment that I thought the success was based on the sorting of the data.

Please Login or Register  to view this content.

If you do want to keep the macro path, then include a step to do the sorting of the data in column B.

rylo

Thanks for replying rylo,

The data is sorted in alphabetical order. The problem is:

USER ENTRY
vehicle

DATABASE ORDER
tester, vehicle
vehicle

RESULT
tester, vehicle

DESIRED
vehicle

The code seems to select tester, vehicle because 't' comes before 'v', if there is only one word entered. The database has several entries where if i type a general word like vehicle, i want the general database entry vehicle to pop up, as there are several entries such as car, vehicle or bus, vehicle etc.

I was thinking of writing an exception for the case of "if the user entry exactly equals the database entry, set the final result cell to the database entry". Is it possible?

I added this

Please Login or Register  to view this content.

shown here

Please Login or Register  to view this content.

but did not work.

I also tried

Please Login or Register  to view this content.

but that did not work either

Jason

Test the attached.

Jindon.

I pretty much understand your code. Thanks for sharing. There was a problem when typing in reverse order e.g.
"alarm bed" gives "alarm bed"
"bed alarm" gives "blank"

Also when trying to type in "tester hearing aid", when the database entry is "tester, hearing aid" results in tester hearing aid. I know why the result is like that. When I replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

as in trying to replace it with the original database
entry value it doesnt work. If r.Value is not the original database entry value, can I ask what is?

Your code is:

Please Login or Register  to view this content.

Ill try having a look at these myself anyway.

If it helps, i've returned the sample data after testing

Thanks for what you've shown so far. It's been really helpful.

abousetta thanks for mentioning that,

Yes I have tried that website already. Still, it makes alot of mistakes with my database and the percentage calculations vary a bit. So far, jindon's and rylo's solutions are better, and much simpler to understand as a newbie.

jindon,

Sorry, should have read a bit more carefully. Here attached is the testing of the code.

jindon,

Just to let you know so far so good, will continue testing.

jindon,

Thanks for helping out. Seems to work pretty well + a few autocorrect touches. I hope other people can benefit from your solution as well.

Jason

That's good and thanks for the feed back.