With 30k rows of data just about any formula using built-in functions will
be slow to calculate. If you'd rather not use a macro are you open to using
an add-in?

--
Biff
Microsoft Excel MVP


"Robert_L_Ross" wrote in message
...
I don't need the dates on Sheet 2 created, I need it to use the date it
finds
on Sheet 2 as the criteria for the count of column B on Sheet 1.

And I need to try to keep it a function, not VB code (our IT group hates
us
using VB - they don't support it).

"Joel" wrote:

If you sort sheet1 with Column E as 1st key and Column B as 2nd key then
run
code below it will create the output you are looking for.

Sub getsummary()

OldDate = ""
OldRowCount = 1
NewRowCount = 1
Unique = 0
With Sheets("Sheet1")
Do While .Range("E" & OldRowCount) < ""
CurrentDate = .Range("E" & OldRowCount)
Num = .Range("B" & OldRowCount)
If CurrentDate = OldDate Then
If Num < OldNum Then
Unique = Unique + 1
OldNum = Num
End If
Else
Unique = 1
OldDate = CurrentDate
OldNum = Num
End If

NewDate = .Range("E" & (OldRowCount + 1))
If CurrentDate < NewDate Then
With Sheets("Sheet2")
.Range("A" & NewRowCount) = OldDate
.Range("B" & NewRowCount) = Unique
NewRowCount = NewRowCount + 1
End With
End If
OldRowCount = OldRowCount + 1
Loop
End With

"Robert_L_Ross" wrote:

In Sheet1 I have:
ColE ColB
1/1/08 00113800
1/1/08 02559000
1/2/08 00113800
1/2/08 02559000
1/2/08 02559000
1/3/08 00113800
1/3/08 02559000

In Sheet2 I Need:
ColA ColB
1/1/08 2
1/2/08 2
1/3/08 2

How can I count unique values on one sheet using criteria from another?
Also, this needs to work on 30,000 rows on Sheet1. I tried a few
solutions
from what I found on the newsgroups (using arrays) and it locked up
Excel.

Thanks!