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

9ballpimp

Board Regular
Joined
Jan 26, 2005
Messages
141
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

mdavide

Board Regular
Joined
Oct 19, 2004
Messages
211
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.

Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Book6
ABCDEFGHI
1Tkt_NNameDateHoursType
2P103Bob7/1/20043Maint7/1/20047/6/200433
3P105Steve7/3/20042.5Enh
4P103Bob7/4/20042Maint
5P104Teri7/6/20043.5Enh
6P106Barry7/8/20042.25Sec
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

Board Regular
Joined
Jan 26, 2005
Messages
141
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
9ballpimp said:
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
ABCDEFGHIJKL
1Tkt_NNameDateHoursTypeMaintEnhSecTotal
2P103Bob7/1/20043Maint7/1/20047/6/200431203
3P105Steve7/3/20042.5Enh
4P103Bob7/4/20042Maint
5P104Teri7/6/20043.5Enh
6P106Barry7/8/20042.25Sec
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top