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!

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!

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!

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!

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.

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.

mike in texas wrote...
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.
....

Perhaps reading the OP's specs carefully would help.

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
....

Create a Pivot Table from the data in Sheet1 as you propose and the
result would be

1/1/08 2
1/2/08 3
1/3/08 2

Note the difference: Pivot Table would return 3 for the 1/2/08 date
while the OP wants 2. If you had read the OP's specs carefully (or
even the subject line), you might have noticed the bit about counting
UNIQUE (meaning counting distinct). There are only 2 distinct col B
values corresponding to the col E value 1/2/08 in the OP's original
data. How would you use a Pivot Table to return the DISTINCT count?

Harlan,

In an off way it would work if I chose to display the ColB results in the
Pivot Table...so I'd provide the date as the first column in the pivot table,
then the Lender ID (ColB) results...the pivot table would then be able to
provide a count of unique Lender ID results.

The only problem with using pivot tables is that the result I need to use as
a source for another sheet...a Pivot Table will vary wildly in the total
number of lines I get. I'm wondering can you use a Pivot Table as a source
for a VLOOKUP and have the Pivot Table act like a range or a named range? If
it can, would a VLOOKUP accept a named range instead of a 'hard' range?
=vlookup(a1,PivotTable1,2,false)
instead of
=vlookup(a1,b1:b30000,2,false)

"Harlan Grove" wrote:

mike in texas wrote...
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.
....

Perhaps reading the OP's specs carefully would help.

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
....

Create a Pivot Table from the data in Sheet1 as you propose and the
result would be

1/1/08 2
1/2/08 3
1/3/08 2

Note the difference: Pivot Table would return 3 for the 1/2/08 date
while the OP wants 2. If you had read the OP's specs carefully (or
even the subject line), you might have noticed the bit about counting
UNIQUE (meaning counting distinct). There are only 2 distinct col B
values corresponding to the col E value 1/2/08 in the OP's original
data. How would you use a Pivot Table to return the DISTINCT count?

Robert_L_Ross wrote...
In an off way it would work if I chose to display the ColB results in the
Pivot Table...so I'd provide the date as the first column in the pivot table,
then the Lender ID (ColB) results...the pivot table would then be able to
provide a count of unique Lender ID results.
....

So where did the OP ask for counts of each distinct col B value rather
than the count of distinct col B values? Why is a 2-step solution with
a pivot table as the first step OK if the second step is left
unmentioned?

Pivot tables are answering a different question than the one the OP
asked.