Sort columns by occurance

[excel_noob_77]

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.

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

 

[oldbrewer]

													a	0.001
Cat	5		col B is a count of col A value in A2:A18							A	0.004
Cat	5												b	0.007
Cat	5		plus VALUE OF FIRST TWO LETTERS IN NAME FROM LOOKUP TABLE				B	0.01
Dog	5		this solves problem of duplications							c	0.013
Dog	5												C	0.016
Dog	5												d	0.019
Dog	5												D	0.022
ant	7												e	0.025
ant	7												E	0.028
cat	5												f	0.031
ant	7												F	0.034
ant	7												g	0.037
ant	7												G	0.04
ant	7												h	0.043
Cat	5												H	0.046
Dog	5												I	0.049
ant	7												I	0.052
													j	0.055
													J	0.058
													k	0.061
ant	7		7 derived by large(B2:B18,1)	ant found by offset match					K	0.064
ant	7					APPLIED TO TOP TABLE					l	0.067
ant	7												L	0.07
ant	7												m	0.073
ant	7												M	0.076
ant	7												n	0.079
Dog	5												N	0.082
Dog	5												o	0.085
Dog	5												O	0.088
Dog	5												p	0.091
Dog	5												P	0.094
Cat	5												q	0.097
Cat	5												Q	0.1
Cat	5												r	0.103
Cat	5												R	0.106
Cat	5												s	0.109
													S	0.112
													t	0.115
													T	0.118
													u	0.121
													U	0.124
													v	0.127
													V	0.13
													w	0.133
													W	0.136
													x	0.139
													X	0.142
													X	0.145
													y	0.148
													Y	0.151
													z	0.154

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[wideboydixon]

This code will add a new temporary column to count the occurrences, sort on that column and then clear the column:

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

 

[excel_noob_77]

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.

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

 

[excel_noob_77]

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.

 

[wideboydixon]

Change the .SetRange line to sort all columns on the sheet:

    .SetRange thisSheet.Range(Cells(1, 1), Cells(lastRow, lastCol))

WBD

 

[excel_noob_77]

Awesome it works. Thanks alot for your help WBD.