CODEWITHSUNDEEP
excelvba
abc
abc

Reputation: 429

VBA - Sort rows based on frequency of text occurrences in column

Is there a way to sort a dynamic table by the frequency of string occurrences in a column? E.g. a column might have these strings from rows 1 to 6: Company A, Company B, Company C, Company B, Company C, Company C. After sorting the table, the row order would be Company C, Company C, Company C, Company B, Company B, Company A

Row Before Sorting Column Text
1 Company A
2 Company B
3 Company C
4 Company B
5 Company C
6 Company C
Row After Sorting Column Text
1 Company C
2 Company C
3 Company C
4 Company B
5 Company B
6 Company A

Upvotes: 2

Views: 425

Answers (2)

David Richardson
David Richardson

Reputation: 147

Do you have a version of Excel with SORT()? If so an assuming your text values to be sorted are in column B:

SORT(B2:B6, -1)

Sorting this dynamically is possible without SORT(), but can be challenging. I can dig out my older spreadsheets to see how I did this previously if you don't have SORT().

EDIT: here's one way to this without using SORT(). I'm sure there are others.

enter image description here

In column B you add this function to get a rank order for each item in column A. You need to drag this formula down for every row with data in column A.

=COUNTIF(A$2:A$13,"<="& $A2)

Now column B has a numerical sort order for each item in column A. There are a few ways to get the sorted list in a different column. For column C I used INDEX() and MATCH(). For column D I used XLOOKUP() with a row offset to match the rank #. They give the same result.

INDEX(A$2:A$12,MATCH(ROWS(B$2:B2), B$2:$B$12, 0))
XLOOKUP(ROW() - 1, B$2:B$12, A$2:A$12)

The XLOOKUP() has the advantage of being easier for most people to understand. It has the disadvantage of assuming the top row of your list won't change.

Editorial you can ignore: The newer array functions in Excel are complete game changers: SORT(), FILTER(), and UNIQUE(). The make doing thing that should be simple and intuitive just that. And they get Excel to being closer to parity with Google Sheets. Still lagging, but closer.

Upvotes: 2

user3259118
user3259118

Reputation:

Thank you for the clarifications in your comments. The following VBA solution should work for your purposes, simply change the sheet/column references to suit.

Option Explicit
Sub SortFrequency()

Dim ws As Worksheet, LastRow As Long, rng As Range, c As Range

Set ws = ThisWorkbook.Sheets("Sheet1")
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A2:A" & LastRow)

For Each c In rng
    c.Offset(0, 1) = Application.WorksheetFunction.CountIf(rng, c)
Next c

rng.Resize(, 2).Sort ws.Cells(2, 2), xlDescending
rng.Offset(, 1).ClearContents

End Sub

Upvotes: 1

Related Questions