Code for displaying top 3 most common numbers within a sigle column

[chanhh]

Hi, I would like to find the top 3 most common numbers within column D using Marco and display the result on the side.
Start counting from D6 all the way till the bottom. (There could be 1000+ rows). Thank you.

Display the following on the side
1st most common : XX
2nd most common: XX
3rd most common: XX

 

[DanteAmor]

Try this:

Sub displaying_top_3()
'
    Application.ScreenUpdating = False
    u = Range("D" & Rows.Count).End(xlUp).Row
    Range("D6:D" & u).Copy Range("E6")
    ActiveSheet.Range("E6:E" & u).RemoveDuplicates Columns:=1, Header:=xlNo
    With Range("F6:F" & Range("E" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=COUNTIF(R6C4:R21C4,RC[-1])"
        .Value = .Value
    End With
    Range("E6:F" & Range("E" & Rows.Count).End(xlUp).Row).Sort key1:=Range("F6"), order1:=xlDescending, Header:=xlNo
    Range("E9:F" & Range("E" & Rows.Count).End(xlUp).Row).Clear
End Sub

You can also get the 3 most common with a pivot table.

 

[chanhh]

Thank you for the code DanteAmor.

However, the code is not 100% correct. I tried modifying it and I couldn't get it worked. I want the values to be displayed on cell starting at U7 (1st common), U8 (2nd Common), U9 (3rd common)

I applied filter to the column D and result is showing as follow:

- A number appears in column D only 5 times (Not 3rd common)
- 2nd common <- Yes
- 1st common <- Yes

 

[DanteAmor]

You can put your test data