Matching unformed name's

[jimsenthil]

Cross-posted at https://www.mrexcel.com/board/thread...-find.1190687/ Solutions have been offered.


how to i compare 2 text values from 2 different cells and show a % values of the similarity. eg: a1 = this is an example b1 = this is an example result : 100% a1 = for an example b1 = this is an example result : 50%

[RaulSerg]

Can you explain your logic?

Why is "for an example" = 50% similar to "this is an example" instead of e.g. 40% or 80% similar?

What you could do, is e.g.: =--EXACT(A1,B1) format as %, which would resultin 100% and 0% otherwise.
This would be case-sensitive while =--(A1=B1) would not be case-sensitive.

E.g. you could argue that "this is an example" and "elpmaxe na si siht" are 0% or 100% similar, depending on whether or not you care about order of elements.

[KOKOSEK]

Maybe you should interest with this:
https://en.wikipedia.org/wiki/Levenshtein_distance
of course there are still issues mentioned by @RaulSerg.

p.s. I've found UDF like this:

Please Login or Register  to view this content.

and gives results like this:

	A	B	C
1	this is an example	this is an example	100%
2	for an example	this is an example	61%
3	elpmaxe na si siht	this is an example	11%
4	jimsenthil	jim senthil	91%

p.s.2. Probably you expect comparing particular words in text, I am afraid. There is still doable in VBA (in formula can be quiet complicated).

Please Login or Register  to view this content.

question is: quantity of existed words (as number) should be divide by quantity of words in text 1 or in text 2 (or with shorter / longer?).
Check 2nd and 3rd rows. The same texts but compare differently: Text1 with Text2 and Text2 with Text1 gives different results.

	A	B	C
1	this is an example	this is an example	100.00%
2	for an example	this is an example	66.67%
3	this is an example	for an example	50.00%
4	elpmaxe na si siht	this is an example	0.00%

[DJunqueira]

A formula for comparing words:

Please Login or Register  to view this content.

[jimsenthil]

I have vlookup or match 2 column, A has unique names, and B has unformatted list of name's , I want match with A column name and want the output with both remark such as 100%,90%,80%,70%,60%,50%,no match..
Pls Suggest an solution on this...

[jimsenthil]

I want to match 2 columns
A is the unique list of name's static
B is unformatted console list
In C want Names that matches,and very close and higher/approximate possibile name to get captured

In D column is any way to put an remark 100%,90%,80%,.....no match...

[AliGW]

This is nonsense:

AliGW on MS365 Insider (Windows) 64 bit

	F	G	H	I
6	for an example	this is an example	for an example	100%
7	this is an example	for an example	this is an example	50%

Sheet: Sheet4

If A compared to B is 100%, then so should B compared to A be 100%.

I very much doubt you are going to be able to programme Excel to do this.

[6StringJazzer]

How can two strings that are not identical be considered a 100% match?

[6StringJazzer]

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.

I have added it to your first post since you are a new member. Please take the time to review our rules. There aren't many, and they are all important.