common row

[jasman]

hi. I have a row of letters from a-b, and I need to find the most common letter in the row. For example:
<TABLE style="WIDTH: 222pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=295 border=0 x:str><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><TBODY><TR style="HEIGHT: 12.75pt; mso-outline-level: 1" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: lime" width=54 height=17>A</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=64>B</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 46pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=61>C</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ff9900" width=59>A</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 43pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #33cccc" width=57>A</TD></TR></TBODY></TABLE>

I was hoping to have a formual at the end of the row which in this case contained 'A' as its the most common letter

thanks

 

[MrKowz]

Is this a simplified version of what you actually have? The reason I ask is because a more complex situation would require a different approach than my initial thought.

 

[jasman]

Hi. No this is it, but there are multiple rows like this. I just need it to tell me at the end of each row the most common letter. Thanks

 

[Peter_SSs]

I just need it to tell me at the end of each row the most common letter.

Have you considered what you want to happen if there is more than one "most common letter"?
- Report all of the "most common" letters?
- Report the first "most common" letter encountered?
- Report an error or some other message like "multiple most common letters"?

Sample data:
C, B, B, A, C

 

[jasman]

If it could just report " more than 1 common letter"

thanks

 

[Peter_SSs]

The formula gurus will quite probably have a formula solution to this but here is a user-defind function attempt. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> MostCommon(r <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, MaxVal <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, MaxCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> bMult <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br> <br>    <SPAN style="color:#00007F">With</SPAN> CreateObject("Scripting.Dictionary")<br>        .CompareMode = vbTextCompare<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> r<br>            s = c.Value<br>            <SPAN style="color:#00007F">If</SPAN> Len(s) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> .Exists(s) <SPAN style="color:#00007F">Then</SPAN><br>                    .Item(s) = .Item(s) + 1<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    .Add s, 1<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>           <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        a = .items<br>        b = .Keys<br>        <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> .Count - 1<br>            <SPAN style="color:#00007F">If</SPAN> a(i) > MaxCount <SPAN style="color:#00007F">Then</SPAN><br>                MaxCount = a(i)<br>                MaxVal = b(i)<br>                bMult = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">ElseIf</SPAN> a(i) = MaxCount <SPAN style="color:#00007F">Then</SPAN><br>                bMult = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">If</SPAN> bMult <SPAN style="color:#00007F">Then</SPAN><br>            MostCommon = "More than 1"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            MostCommon = MaxVal<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br></FONT>

Excel Workbook
	A	B	C	D	E	F
1						Most Common
2	A	B	C	A	A	A
3	C	B	B	A	C	More than 1
4	A	B	B	C	D	B
5	A	B	C	D	E	More than 1
6	C	C	C	C	C	C
Most Common Letter