How to sort unique cells in order of appearance on google sheets?

Question

So, I know there is a easy way to sort unique values in alphabetic order using

SORT(UNIQUE(),1,TRUE)

but i'm trying to order based on how many times a item has appeared.

exemple, if had a column like

1. Apple
2. Apple
3. Banana
4. Banana
5. Banana
6. Lemon

I would like a return like:

1. Banana
2. Apple
3. Lemon

Answer 1

With your list in A1:A6 you can try:

=unique(sort(A1:A6,countif(A1:A6,A1:A6),0))

N.B I've just realised you've tagged the question with Excel as well as Google Sheets - this formula only works in Google Sheets as the Excel implementation of SORT is different.

Answer 2

One way is to use the query() function:

=query(A1:A, "select A, count(A) group by A order by count(A) desc", 0)

This has the benefit that it also shows the counts per value.

If the column has a header row, you can show that header on top of the results by replacing the 0 with a 1.

In the event the column includes blank values, you can leave out that count like this:

=query(A1:A, "select A, count(A) where A is not null group by A order by count(A) desc", 0)