Hi

Look at this, output will be in Sheet1:

Sub AAA()
Dim TargetSh As Worksheet
Dim DestSh As Worksheet
Dim FilterRange As Range
Set TargetSh = Worksheets("Dist")
Set DestSh = Worksheets("Sheet1") ' Change to suit
FirstRow = 1 ' HeaderRow
TargetCol = "A" ' Change to suit
LastRow = TargetSh.Range(TargetCol & Rows.Count).End(xlUp).Row
Set FilterRange = TargetSh.Range(TargetCol & FirstRow, _
TargetSh.Range(TargetCol & LastRow))
FilterRange.AdvancedFilter Action:=xlFilterInPlace, _
Unique:=True
FilterRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=DestSh.Range("A1")
CellCount = FilterRange.SpecialCells(xlCellTypeVisible).Cells. Count
TargetSh.ShowAllData
DestSh.Range("B2").FormulaR1C1 = "=COUNTIF(Dist!R2C1:R7C1,RC[-1])"
DestSh.Range("B2").AutoFill Destination:=DestSh.Range _
("B2:B" & CellCount), Type:=xlFillDefault
End Sub

Regards,
Per

On 7 Maj, 01:10, "medirate" wrote:
I have a column of text where I need to count the unique values. The column
of text is in another worksheet called Dist. The column of data may look
like this:

24509
13450
24509
18709
24509
13450

I have no idea what the column of data may contain beforehand, so I can't do
a count of "24509", but I need the result to look like this:

24509 * *3
13450 * *2
18709 * *1

Any suggestions?

Thanks!