Counting Duplicate enties within a Date

Hi Tee,

Here is a little custom VBA function that you can use from a worksheet to count the number of unique entries in a range of cells. Perhaps you can use this to do what you want.

Happy computing.

Damon

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: VBAexpert@piadamon.com

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

Many thanks for that - it is just want I wanted but how do I get it to count within a certain date. I have tried
=SUM((MONTH(VISDate)=1)*(CountUnique(DB!B2:B41)))
but it is counting in the whole range not just the month of Jan (1). any ideas. the range
DB!B2:B41 is Jan and Feb.

Tee 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

Hi Tee,

Here's one way to do it. I modified the routine I gave you slightly so it would only count entries that are not in hidden rows. Thus you can now filter on the date column to display only the dates you want, and the function will only count the dates that are displayed. If, for example, you want to count the unique values for January, temporarily format your date column to display only month. Then add a filter to this column and filter for month=1. CountIFunique will display the unique number of values for that month that are unique.

Here's the updated function:

Function CountIFunique(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: VBAexpert@piadamon.com

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

nItems = ItemList.Cells.Count
CountIFUnique = 0
For i = 1 To nItems
If Not ItemList.Rows(i).Hidden Then
For j = 1 To i - 1
If Not ItemList.Rows(j).Hidden Then
If ItemList.Cells(i) = ItemList.Cells(j) Then GoTo Duplicate
End If
Next j
CountIFUnique = CountIFUnique + 1
End If
Duplicate:
Next i

End Function

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

Counting TYPES within a Date range

Damon & Tee,

If you're not per se after a single formula, a formula-based solution is also possible.

I'll assume your example data

{35430," V0100";35434," V0100";35434," V0100";35449," V0100";35465," V0145";35484," V0178";35503," V0123";35508," V0199";35508," V0199";35535," V0101 "}

to be in A2:B11. (Those numbers are dates as internally represented, produced by the procedure that I used to re-post the data. )

In C2 enter: 01-02-2001 (start of the criterion date-range)
In C3 enter: 28-02-2001 (end of the crireion date-range)
In C4 enter: =COUNTIF(D:D,"*?*")

In D2 enter: =IF(AND(A2>=$C$2,A2<=$C$3),IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),"",B2),"") [ copy down as far as needed ]

In C4 you'll see the desired count.

A suitable VBA code could perhaps pick up the criterion range and the count and put them somewhere to consolidate. Damon, any comments on this possiblity?

Aladin

===============

Here's one way to do it. I modified the routine I gave you slightly so it would only count entries that are not in hidden rows. Thus you can now filter on the date column to display only the dates you want, and the function will only count the dates that are displayed. If, for example, you want to count the unique values for January, temporarily format your date column to display only month. Then add a filter to this column and filter for month=1. CountIFunique will display the unique number of values for that month that are unique. Here's the updated function: Function CountIFunique(ItemList As Range) As Long