formula - Excel: Find most frequent occurring value in a range?

Question

I have a column on sheet 1 like so:

Column D
    Dog
    Dog
    Dog
    Dog
    Cat
    Cat
    Cat
    Hamster
    Frog
    Frog

On sheet 2, i want to list the top 10 most frequent occurring words in chronological order

Dog .  <---Most Frequent
Cat .  <---Second Frequent
Frog . <--Third
etc.

I am trying to use index, mode and match like so:

=INDEX(Sheet1!D:D,MODE(MATCH(Sheet1!D:D,Sheet1!D:D,0)))

This produces an N/A error

But if i add ranges to my column reference like so:

=INDEX(Sheet1!D1:D10,MODE(MATCH(Sheet1!D1:D10,Sheet1!D1:D10,0)))

Then it works.

Problem is data is going to be added to columnn D and i need it to look up the entire column D.

Please can someone show me where i am going wrong?

Answer 1

If we use a helper column we can completely eliminate the need for array formulas. In E1 enter 1 and in E2 enter:

=IF(COUNTIF($D$1:D1,D2)>0,"",1+MAX($E$1:E1))

and copy down as far as column D. (as items are added to column D, pull the column E formula down as well)

Finally in F1 enter:

=IFERROR(INDEX(D:D,MATCH(ROWS($1:1),E:E,0)),"")

and copy down. In G1 enter:

=COUNTIF(D:D,F1)

and copy down:

NOTE:

The little frequency table in columns F and G is "sort-able".