Excel Formula

[Tvynn]

[TABLE="width: 1337"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]
[TABLE="width: 692"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Hi, refer to attachment, I'd like to find out how many time the email at column A appears at col. B (within the same row only. Eg. A1 with B1, A2 with B2). Currently I have this formula as appeared at col. C1, but it's not that accurate, as long as the emails at col. B are longer that email at col. A, it will count it. Wonder if you knows the accurate formula to count it? Much appreciated! Many thanks for your help!

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1681"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Email[/TD]
[TD]Range[/TD]
[TD]Formula
=(LEN(C2)-LEN(SUBSTITUTE(UPPER(C2),UPPER(A2),"")))/LEN(A2)[/TD]
[TD]Accurate Result[/TD]
[/TR]
[TR]
[TD]A@abc.com[/TD]
[TD]A@abc.com.sg/connection lost, A@abc.com.sg/connection lost, AA@abc.com.sg/unknown, A@abc.com/error[/TD]
[TD]4[/TD]
[TD]wrong, should be 1[/TD]
[/TR]
[TR]
[TD]bb@mail.com.sg[/TD]
[TD]bb@gmail.com.sg/connection lost, aa@mail.com.sg/connection lost, bb@mail.com/error[/TD]
[TD]0[/TD]
[TD]correct[/TD]
[/TR]
[TR]
[TD]ab@outlook.com[/TD]
[TD]aab@outlook.com/connection lost, aab@outlook.com.my/connection lost, ab@outlook.my/connection lost[/TD]
[TD]2[/TD]
[TD]wrong, should be 0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe...
=(LEN(","&SUBSTITUTE(C2," ",""))-LEN(SUBSTITUTE(UPPER(","&SUBSTITUTE(C2," ","")),UPPER(","&A2&"/"),"")))/LEN(","&A2&"/")

Hope this helps

M.

 

[pwhatley]

I am going to offer a different approach using the Search function. The search function matches whether the string to be searched for is upper or lower case, (The Find function requires matching case.)

Bear in mind, this is not exactly what you asked for as it returns a 0 if the email address is not found or a 1 if it is found. Although if there is a small number of error types, you could add additional formulas which include the error type in the search string, e.g., "/connection lost", etc.

Also, I had to add the space, (" "&) so that aab@outlook.com would not be counted even though it contains ab@outlook.com

=IF(ISERROR(SEARCH(" "&$A4&"/"," "&$B4)),0,1)

I hope this helps.

Paul

 

[pwhatley]

I took one more shot at this and now have the formula counting the occurrences of the email address within the string, so if the email is listed more than once, e.g., with more than one error, it should count it as many times.

At least it worked for me when I dummied a record with an email address listed twice.

=(LEN(" "&$C4&"/")-LEN(SUBSTITUTE(UPPER(" "&$C4&"/"),UPPER(" "&$A4&"/"),"")))/(LEN($A4)+2)