I'd suggest a pivot table on sheet 2. Set it up with dates in the rows and
use the "count" of date in the data field.

I did 25,000 records in a few seconds.

Good luck


Mike


"Harlan Grove" wrote:

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

If your source data above is sorted on col E, then the most efficient
way to handle this uses additional columns of formulas in Sheet1. If
columns X, Y and Z are available, your first row of data is 2 and your
last is 30001, enter the following formulas.

X30002:
=ROW()

Y30002:
=X30002

X2:
=IF(E2<E1,ROW(),"")

Y2:
=IF(N(X2),IF(N(X3),X3,Y3)-1,IF(N(X3),X3,Y3))

Z2:
=IF(E2<E1,SUMPRODUCT(1/COUNTIF(INDEX(B:B,X2):INDEX(B:B,Y2),INDEX
(B:B,X2):INDEX(B:B,Y2))),"")

Fill X2:Z2 down into X3:Z30001.

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

From your follow-up post it seems col A in Sheet2 is already filled.
If so, the B2 formula for the date in A2 is given by the formula

B2:
=VLOOKUP(A2,Sheet1!E:Z,22,0)

This is the most recalc efficient way to do this I've found. It'll
still be quite slow with 30K records.

If you need to do this a lot, you need to show your IT people this
mess of formulas that you'd need to use and tell them that if they
provided you a decent SQL database (so NOT Access) to use, this could
be done simply as

SELECT ColE, COUNT(DISTINCT ColB) FROM Table GROUP BY ColE;

IOW, if they refuse to give you the best tool for the task, they
should have to support what you have to hack together. They should
welcome a decent VBA alternative to the mess of formulas above, but if
not, make it very clear that THEY get to maintain all these formulas
when you move on to greener pastures.