Getting most common values using mode in excel

Question

I have a list of 65 random numbers starting in column D2. I am trying to make a new list of number in column J2 that arranges these numbers by most frequent number.

I did this by tying the below formula into j2:

=MODE(IF(1-ISNUMBER(MATCH($D$2:$D$66,$J$1:J1,0)),$D$2:$D$66))

The problem is some numbers from column D are not appearing in column J. why is this?

Answer 1

With values in D2 through D66, in E2 enter:

=COUNTIF($D$2:$D$66,D2)

and copy downwards:

Then sort both columns by column E descending:

This will capture all 65 items. If desired, you can perform a secondary sort on column D to cluster similar values.