How to sort a column by occurrence count in Excel?

Question

Let's say I have a column like this:

Dog
Bird
Cat
Dog
Dog
Dog
Bird
Cat
Bird

It has 4 Dog, 3 Bird and 2 Cat

I want to sort this column descending by these count numbers.

I mean result should be this when I sort it:

Dog
Dog
Dog
Dog
Bird
Bird
Bird
Cat
Cat

How can I do that in Excel 2007?

Answer 1

1. In the column next to the group insert the following formula

=COUNTIF(A$1:A$9, A1)

2. Highlight the cells you want to sort.
3. Click on the Data tab on top of the ribbon
4. In the Sort section, select Sort. In the dialog box select the second column and then from highest to lowest.

Answer 2

For me (using Excel 2016 on Win8) this answer doesn't work. It resorts the FORMULAS which now don't refer to the field in the same row...

The way to avoid this is to use =COUNTIF(Sheet2!$A$1:$A$100,INDEX(A:A,ROW()))

Solution explained in Excel Forum - Sorting data containing COUNTIF formulas

Answer 3

For @wbeard52's solution - Excel 2016 on Windows 10, instead of , it should be a ; as delimiter. Like this:

=COUNTIF(A$1:A$9;A1)

Tip: If you have many rows, write the two first rows like this:

=COUNTIF(A$1:A$10000;A1)
=COUNTIF(A$1:A$10000;A2)

Then copy these two cells, mark cell 3 to 10000 in this case and then paste the code. I did this with 150k + rows and it worked like a charm. However I had to restart Excel after setting the formula for the correct values to show up.