Sort columns by occurance

excel_noob_77

New Member
Joined
Jun 26, 2017
Messages
6
Hey guys,

I am trying to sort a column by the occurance (or count) of a particular word. So for example if I had a column similar to this

Cat
Cat
Cat
Dog
Dog
Dog
Dog

It would sort the column based on their count (the items with the most occurance would be listed first and so forth). So it would be

Dog
Dog
Dog
Dog
Cat
Cat
Cat

Already happened to find a partial code which does a count similar to the code shown below. How should i modify this VBA code to then sort the column by frequency/count of a particular word. Could someone help me out with this.

Code:
Sub SortMessageByCount()
Dim Mode_Client As String
Range("A" & Rows.Count).FormulaArray = _
"=INDEX(A2:A26,MODE(MATCH(A2:A26,A2:A26,0)))"
Mode_Client = Range("A" & Rows.Count)
Range("A" & Rows.Count).ClearContents
MsgBox "The message occuring the most is " & Mode_Client
End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
[TABLE="width: 1024"]
<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]0.001[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl22"]Cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD="colspan: 4"]col B is a count of col A value in A2:A18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]0.004[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]Cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD="align: right"]0.007[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]Cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD="colspan: 7"]plus VALUE OF FIRST TWO LETTERS IN NAME FROM LOOKUP TABLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]0.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD="colspan: 4"]this solves problem of duplications[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]c[/TD]
[TD="align: right"]0.013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]0.016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]d[/TD]
[TD="align: right"]0.019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]0.022[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]e[/TD]
[TD="align: right"]0.025[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD="align: right"]0.028[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]f[/TD]
[TD="align: right"]0.031[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD="align: right"]0.034[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]g[/TD]
[TD="align: right"]0.037[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD="align: right"]0.04[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]h[/TD]
[TD="align: right"]0.043[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]Cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]H[/TD]
[TD="align: right"]0.046[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I[/TD]
[TD="align: right"]0.049[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl22"]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I[/TD]
[TD="align: right"]0.052[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]j[/TD]
[TD="align: right"]0.055[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]J[/TD]
[TD="align: right"]0.058[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]k[/TD]
[TD="align: right"]0.061[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD="colspan: 3"]7 derived by large(B2:B18,1)[/TD]
[TD="colspan: 3"]ant found by offset match[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]K[/TD]
[TD="align: right"]0.064[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]APPLIED TO TOP TABLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]l[/TD]
[TD="align: right"]0.067[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L[/TD]
[TD="align: right"]0.07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]m[/TD]
[TD="align: right"]0.073[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD="align: right"]0.076[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ant[/TD]
[TD="class: xl23, align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]n[/TD]
[TD="align: right"]0.079[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N[/TD]
[TD="align: right"]0.082[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]o[/TD]
[TD="align: right"]0.085[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD="align: right"]0.088[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]p[/TD]
[TD="align: right"]0.091[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]P[/TD]
[TD="align: right"]0.094[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]q[/TD]
[TD="align: right"]0.097[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]r[/TD]
[TD="align: right"]0.103[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD="align: right"]0.106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD="class: xl23, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]s[/TD]
[TD="align: right"]0.109[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD="align: right"]0.112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]t[/TD]
[TD="align: right"]0.115[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T[/TD]
[TD="align: right"]0.118[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]u[/TD]
[TD="align: right"]0.121[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD="align: right"]0.124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]v[/TD]
[TD="align: right"]0.127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]V[/TD]
[TD="align: right"]0.13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]w[/TD]
[TD="align: right"]0.133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]W[/TD]
[TD="align: right"]0.136[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD="align: right"]0.139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD="align: right"]0.142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD="align: right"]0.145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]y[/TD]
[TD="align: right"]0.148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD="align: right"]0.151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]z[/TD]
[TD="align: right"]0.154[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
This code will add a new temporary column to count the occurrences, sort on that column and then clear the column:

Code:
Public Sub SortByOccurrence()

Const SortColumn = "A" ' Column A - change to suit

Dim lastRow As Long
Dim lastCol As Long
Dim sortRange As Range
Dim cellRange As Range
Dim thisSheet As Worksheet

' Set the sheet
Set thisSheet = ActiveSheet

' Find the last row
lastRow = thisSheet.Cells(thisSheet.Rows.Count, SortColumn).End(xlUp).Row

' Find the last column
lastCol = thisSheet.Cells(1, thisSheet.Columns.Count).End(xlToLeft).Column + 1

' Set the ranges
Set cellRange = thisSheet.Range(thisSheet.Cells(1, SortColumn), thisSheet.Cells(lastRow, SortColumn))
Set sortRange = thisSheet.Range(thisSheet.Cells(1, lastCol), thisSheet.Cells(lastRow, lastCol))

' Create a new column with the formula
sortRange.Formula = "=COUNTIF(" & cellRange.Address & ",$" & SortColumn & "1)"

' Now do the sorting
With thisSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=sortRange, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .SortFields.Add Key:=cellRange, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange thisSheet.Range(Cells(1, SortColumn), Cells(lastRow, lastCol))
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlSortColumns
    .SortMethod = xlPinYin
    .Apply
End With

' Clear the temporary column
sortRange.ClearContents

End Sub

Change the SortColumn to be the column that you're sorting. I've assumed no column headers.

WBD
 
Upvote 0
So how would i translate that into VBA code is that by having. Sorry im quite new to this. Would it be something similar to this.

Code:
Sub SortMessageByArea()
Dim Mode_Client As String
Range("C" & Rows.Count).FormulaArray = _
"=[B]OFFSET[/B](INDEX(B2:B26,MODE(MATCH(B2:B26,B2:B26,0))),-1,0)"
Mode_Client = Range("C" & Rows.Count)
Range("C" & Rows.Count).ClearContents
Range("C1").Sort Key1:=Range("C2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
MsgBox "The message occuring the most by area is " & Mode_Client
End Sub
 
Upvote 0
Cheers this work fine. Just one issue though how would i sort columns right of the active column cause it sort all the columns to the left of the selection. How would i modify the VBA to consider all the columns in the sheet.
 
Upvote 0
Change the .SetRange line to sort all columns on the sheet:

Rich (BB code):
    .SetRange thisSheet.Range(Cells(1, 1), Cells(lastRow, lastCol))

WBD
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top