HELP! Unique column count based on date range...

[9ballpimp]

I hope that someone can help! I have a spreadsheet with, say, 250 rows(constantly growing), but only 5 columns...
The table is set up as such:

A B C D E
Tkt_N Name Date Hours Type
1 P103 Bob 7/1/04 3.00 Maint
2 P105 Steve 7/3/04 2.50 Enh
3 P103 Bob 7/4/04 2.00 Maint
4 P104 Teri 7/6/04 3.50 Enh
5 P106 Barry 7/8/04 2.25 Sec
.
.
.

What I need to do is for a particular date range (a begin and end date, which will constantly change)using column C, I need to count the number of UNIQUE records in Tkt_N(COL A, which is both text and numerical). For instance, say my date range I'm pulling is 7/1/04-7/6/04. I need it to bring back an answer of "3" (unique ticket numbers from Column A).

What makes it difficult is the date range is always changing...Can anyone please help? Thanks!

9ballpimp

 

[mdavide]

HI,

I think you could use this UDF. copy it inside a new VBA module, and then use it like a normal formula.
the first argument is a range, it must be a 4 columns range like your example, the second and the third argument are strings and refer to the start date and to the end date.
the formula returns a count of unique values found in column 1.

Option Base 1

Public Function countunique(ByVal where As Range, ByVal startdate As String, ByVal enddate As String)
Dim ws As Worksheet
Dim i As Long, j As Long
Dim tabulist() As Variant
Dim ntabulist As Long
Dim nrows As Long
Dim start As Date
Dim ending As Date


nrows = where.rows.Count

If where.Columns.Count <> 4 Then
    countunique = "#VALUE"
    Exit Function
End If
If (IsDate(startdate) Or IsDate(enddate)) = False Then
    countunique = "#VALUE"
    Exit Function
End If
If (DateValue(startdate) > DateValue(enddate)) Then
    countunique = "#VALUE"
    Exit Function
End If

Set ws = where.Parent
For j = 1 To nrows
    If where.rows(j).Columns(2) >= DateValue(startdate) And where.rows(j).Columns(2) <= DateValue(enddate) Then
        If ntabulist = 0 Then
            ntabulist = ntabulist + 1
            ReDim tabulist(1)
            tabulist(1) = where.rows(j).Columns(1).Value
            countunique = 1
        Else
            For i = 1 To ntabulist
                If where.rows(j).Columns(1).Value = tabulist(i) Then GoTo found
            Next
            ntabulist = ntabulist + 1
            ReDim Preserve tabulist(ntabulist)
            tabulist(ntabulist) = where.rows(j).Columns(1).Value
            countunique = countunique + 1
found:
        End If
    End If
Next

I hope this helps you

DM

 

[Domenic]

Or you can try the following...

=SUM(IF(FREQUENCY(IF((C2:C6>=F1)*(C2:C6<=G1),MATCH(A2:A6,A2:A6,0)),ROW(INDIRECT("1:"&ROWS(A2:A6))))>0,1))

OR

=COUNT(1/FREQUENCY(IF((C2:C6>=F1)*(C2:C6<=G1),MATCH(A2:A6,A2:A6,0)),ROW(INDIRECT("1:"&ROWS(A2:A6)))))

...where F1 contains the start date and G1 contains the end date. Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Aladin Akyurek]

Book6
	A	B	C	D	E	F	G	H	I
1	Tkt_N	Name	Date	Hours	Type
2	P103	Bob	7/1/2004	3	Maint	7/1/2004	7/6/2004	3	3
3	P105	Steve	7/3/2004	2.5	Enh
4	P103	Bob	7/4/2004	2	Maint
5	P104	Teri	7/6/2004	3.5	Enh
6	P106	Barry	7/8/2004	2.25	Sec
7
Sheet1

Formulas...

1] For a static range...

H2:

=COUNT(1/FREQUENCY(IF((A2:A6<>"")*(C2:C6>=F2)*(C2:C6<=G2),MATCH(A2:A6,A2:A6,0)),ROW(INDEX(A2:A6,0,0))-ROW(A2)+1))

which must be confirmed with control+shift+enter instead of the usual enter.

2] For a dynamic (crisping/expanding) range...

G2:

=COUNT(1/FREQUENCY(IF((Tnums<>"")*(Dates>=F2)*(Dates<=G2),MATCH(Tnums,Tnums,0)),ROW(INDEX(Tnums,0,0))-ROW(INDEX(Tnums,1,1))+1))

which also needs to be confirmed with control+shift+enter instead of the usual enter.

Moreover, the latter requires the following definitions (using Insert|Name|Define)...

Name: BigNum
Refers to:

=9.99999999999999E+307

Name: Tnums
Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,MATCH(BigNum,Sheet1!$C$2:$C$65536))

Name: Dates
Refers to:

=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$65536,MATCH(BigNum,Sheet1!$C$2:$C$65536))

The foregoing formulas are less volatile and can handle empty/blank cells in the ranges of interest.

 

[9ballpimp]

Thanks to all! That part works. Now, I have another part...Out of the unique records that are totaled within the date range, I also need to do a separate count of the # of tickets of each type, based on the Type column (which can be either Maint, Enh or Sec only). Is there an easy way to integrate that into this?

9ballpimp

 

[Aladin Akyurek]

Thanks to all! That part works. Now, I have another part...Out of the unique records that are totaled within the date range, I also need to do a separate count of the # of tickets of each type, based on the Type column (which can be either Maint, Enh or Sec only). Is there an easy way to integrate that into this?

9ballpimp

aaCondUniqueCount 9ballpimp.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	Tkt_N	Name	Date	Hours	Type				Maint	Enh	Sec	Total
2	P103	Bob	7/1/2004	3	Maint	7/1/2004	7/6/2004	3	1	2	0	3
3	P105	Steve	7/3/2004	2.5	Enh
4	P103	Bob	7/4/2004	2	Maint
5	P104	Teri	7/6/2004	3.5	Enh
6	P106	Barry	7/8/2004	2.25	Sec
7
Sheet1

Formulas...

Define first Types...

Name: Types
Refers to:

=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$65536,MATCH(BigNum,Sheet1!$C$2:$C$65536))

H2: (see the previous reply)...

=COUNT(1/FREQUENCY(IF((Tnums<>"")*(Dates>=F2)*(Dates<=G2),MATCH(Tnums,Tnums,0)),ROW(INDEX(Tnums,0,0))-ROW(INDEX(Tnums,1,1))+1))

followed by control+shift+enter. This can be dismissed for a total of the results by the following formulas would take care of the outcome this formula produces.

I2:

=COUNT(1/FREQUENCY(IF((Tnums<>"")*(Types=I$1)*(Dates>=$F2)*(Dates<=$G2),MATCH(Tnums,Tnums,0)),ROW(INDEX(Tnums,0,0))-ROW(INDEX(Tnums,1,1))+1))

which must be confirmed with control+shift+enter and copied across to K2.

L2:

=SUM(I2:K2)