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



## M0Dark (Dec 16, 2022)

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 (Dec 16, 2022)

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 (Dec 17, 2022)

With a formula.
In BA2 
	
	
	
	
	
	



```
=UNIQUE(TOCOL(P2:AZ50,1))
```
and in BB2 
	
	
	
	
	
	



```
=COUNTIFS(P2:AZ50,BB2#)
```


----------

