Formula

[User279]

How do I create a simple counter to show repeating values?

 

[John Bundy]

It all depends what you are trying to count and how you want to show it. are
you wanting something that increments automatically as a duplicate is entered
or like a button that takes a number up and down? Or just a count of all
duplicate data?

 

[User279]

Yes something that increments automatically as duplicates are entered.

 

[John Bundy]

Here is a formula from way back that counts uniques, you can change to bring
back duplicates or just subtract the result from the count of cells.

Function CountUnique(ItemList As Range) As Long

' CountUnique counts the number of cells in a range of cells, but only
' counts cells that have duplicates once. Thus it is like the Excel
' COUNT function except that it only counts unique values.

' Written by: Damon Ostrander
' Date: 5/3/01
' Email: (e-mail address removed)

Dim nItems As Long
Dim i As Long
Dim j As Long

nItems = ItemList.Cells.Count
CountUnique = 0
For i = 1 To nItems
For j = 1 To i - 1
If ItemList.Cells(i) = ItemList.Cells(j) Then GoTo Duplicate
Next j
CountUnique = CountUnique + 1
Duplicate:
Next i

End Function

 

[User279]

Thank you, I'll try it

Here is a formula from way back that counts uniques, you can change to bring
back duplicates or just subtract the result from the count of cells.

Function CountUnique(ItemList As Range) As Long

' CountUnique counts the number of cells in a range of cells, but only
' counts cells that have duplicates once. Thus it is like the Excel
' COUNT function except that it only counts unique values.

' Written by: Damon Ostrander
' Date: 5/3/01
' Email: (e-mail address removed)

Dim nItems As Long
Dim i As Long
Dim j As Long

nItems = ItemList.Cells.Count
CountUnique = 0
For i = 1 To nItems
For j = 1 To i - 1
If ItemList.Cells(i) = ItemList.Cells(j) Then GoTo Duplicate
Next j
CountUnique = CountUnique + 1
Duplicate:
Next i

End Function