Sort, List, and Count repetitions of values within a range

[M0Dark]

I have a range of cells (P2:AZ50) which contain String values and blank cells. What I want to do is get a list of all the discrete values out of that range in column BA and a count off the occurrences in column BB.
The data changes with cell values being changed / replaced with blanks etc, but not frequently enough that a manual macro button (for example) to recompile the list & count would be a problem.
Please advise the best way to accomplish this.

Many thanks.

 

[Akuini]

Try:

Sub test1()
Dim x, va
Dim d As Object

Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare

va = Range("P2:AZ50")
    For Each x In va
        d(x) = d(x) + 1
    Next

If d.Exists("") Then d.Remove ""

'put the result in col BA:
Range("BA2").Resize(d.Count, 2) = Application.Transpose(Array(d.Keys, d.Items))

End Sub

 

[Fluff]

With a formula.
In BA2

=UNIQUE(TOCOL(P2:AZ50,1))

and in BB2

=COUNTIFS(P2:AZ50,BB2#)