• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match text and rate

T

Thomas Kuriakose

Active Member
Respected Sirs,

We have a file where we enter two texts and this is required to be matched for each character and the unmatched characters need to be counted and rated based on the matrix.

Kindly find attached the file for your reference. The formula required is in cells O2 and O5 for the count of unmatched cells and then rating in cells P2 and P5. A single formula for the rating based on the unmatched characters will be good.

kindly suggest a better way to go about this.

Thank you very much.

with regards,
thomas
 

Attachments

  • Match Texts.xlsx
    10.6 KB · Views: 15
To count mismatch, try something like...
=SUM(--ISERROR(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),A2)))

Confirmed as array (CTRL + SHIFT + ENTER).

Then you can just use INDEX/MATCH or other lookup type formula for the rating.

Edit: You can replace Indirect with INDEX formula construct.
=SUM(--ISERROR(SEARCH(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1),)),1),A2)))

Confirmed as array as well.
 
Last edited:
Wait... looking at your sheet, you want the count of match and not mismatch?

If that's the case, you can use.
=SUM(--ISNUMBER(SEARCH(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1),)),1),A2)))

Confirmed as array (CTRL + SHIFT + ENTER)
 
Or…….

1] Count of unmatched (non-array formula) :

=LEN(A1)-COUNT(SEARCH(MID(A1,MMULT(ROW(INDIRECT("1:"&LEN(A1))),1),1),A2))

2] Count of matched (non-array formula) :

=COUNT(SEARCH(MID(A1,MMULT(ROW(INDIRECT("1:"&LEN(A1))),1),1),A2))

Regards
Bosco
 
Last edited:
You must log in or register to reply here.
Back
Top