• 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.

Count Common Letters

Ehtisham Ali

Ehtisham Ali

Member
Hi All,

Please refer to attached sheet and suggest a formula to calculate the field shown.
 

Attachments

  • query1.xlsx
    9.9 KB · Views: 8
Mike H.. said:
Check out your workbook.
Click to expand...
hi gr8 work sir but i need more functionality over it.

If u see the names
Jameel Areeb = 3

but if i change it to
Jameel Areb -- again = 3 but it should be 2 according to my requirement.

I hope I am clear with my question now.
 
Ehtisham Ali said:
hi gr8 work sir but i need more functionality over it.

If u see the names
Jameel Areeb = 3

but if i change it to
Jameel Areb -- again = 3 but it should be 2 according to my requirement.

I hope I am clear with my question now.
Click to expand...
Hi,

I see now, you want unique occurrences, see the answer from NARAYANK991 above.
 
I have tried to demonstrate my actual requirement in below SS please refer.

Yellow figures are the actual answers I want.
Leave the letters striked through and count only those remainingREQ.JPG .
 
Hi Ali,

I think your requirement in post #1 & post# 7 are different: if so than try below formula for #7, with various samples. Can't some up with a descent formula, may be somebody will show the magic.

=MIN(SUM(FREQUENCY(IFERROR(MATCH(MID(J12,ROW(INDIRECT("1:"&LEN(J12))),1),MID(K12,ROW(INDIRECT("1:"&LEN(K12))),1),0),"e"),ROW(INDIRECT("1:"&LEN(J12))))),SUM(FREQUENCY(IFERROR(MATCH(MID(K12,ROW(INDIRECT("1:"&LEN(K12))),1),MID(J12,ROW(INDIRECT("1:"&LEN(J12))),1),0),"e"),ROW(INDIRECT("1:"&LEN(K12))))))

This will give no. of common character. Names are in J12 & K12, and below formula will give the result as shown in your pic.

=SUM(LEN(J12:K12))-(MIN(SUM(FREQUENCY(IFERROR(MATCH(MID(J12,ROW(INDIRECT("1:"&LEN(J12))),1),MID(K12,ROW(INDIRECT("1:"&LEN(K12))),1),0),"e"),ROW(INDIRECT("1:"&LEN(J12))))),SUM(FREQUENCY(IFERROR(MATCH(MID(K12,ROW(INDIRECT("1:"&LEN(K12))),1),MID(J12,ROW(INDIRECT("1:"&LEN(J12))),1),0),"e"),ROW(INDIRECT("1:"&LEN(K12))))))*2)

Note both the formula are array formulas so must be entered with Ctrl+Shift+Enter.

Regards,
 
One more idea on similar lines. Array formula for cell E5:
=SUM(IF(FREQUENCY(SEARCH(MID(C5,ROW($A$1:INDEX(A:A,LEN(C5))),1),"abcdefghijklmnopqrstuvwxyz"),ROW($A$1:$A$26))<=FREQUENCY(SEARCH(MID(D5,ROW($A$1:INDEX(A:A,LEN(D5))),1),"abcdefghijklmnopqrstuvwxyz"),ROW($A$1:$A$26)),FREQUENCY(SEARCH(MID(C5,ROW($A$1:INDEX(A:A,LEN(C5))),1),"abcdefghijklmnopqrstuvwxyz"),ROW($A$1:$A$26)),FREQUENCY(SEARCH(MID(D5,ROW($A$1:INDEX(A:A,LEN(D5))),1),"abcdefghijklmnopqrstuvwxyz"),ROW($A$1:$A$26))))

You can create a named range to whittle it down. Your result in E8 is incorrect as there are 2 matching e's and 1 matching a so it should be three.
 
Did not see the requirement in #7. It can be probably met like below (normally entered):
=SUMPRODUCT(ABS(FREQUENCY(SEARCH(MID(C5,ROW($A$1:INDEX(A:A,LEN(C5))),1),"abcdefghijklmnopqrstuvwxyz"),ROW($A$1:$A$26))-FREQUENCY(SEARCH(MID(D5,ROW($A$1:INDEX(A:A,LEN(D5))),1),"abcdefghijklmnopqrstuvwxyz"),ROW($A$1:$A$26))))
Please test and see if the results are what you need.
 
hi Somendra and Shrivallabha,

You guys are exactly what chandoo's tagline says AWESOME.

=SUMPRODUCT(ABS(FREQUENCY(SEARCH(MID(C5,ROW($A$1:INDEX(A:A,LEN(C5))),1),"abcdefghijklmnopqrstuvwxyz"),ROW($A$1:$A$26))-FREQUENCY(SEARCH(MID(D5,ROW($A$1:INDEX(A:A,LEN(D5))),1),"abcdefghijklmnopqrstuvwxyz"),ROW($A$1:$A$26))))

this one is really gr8 formula, now I understand why u people are called Ninja's.

Thanks fellas for your prompt reply and gr8 help.
 
You must log in or register to reply here.
Back
Top