count unique records with latest date
 

I Want To Count All "x" With Unique Record In Column B
Parameter Must Be The Latest Date In Colum C Will Count

Answer Is "2"
Date Of 10/01/05 08:01am
Ed & Xr

What Is The Easyway To Create This Formula
Tnx


A B C


X Ed 10/01/05-08:00am
X Ed 10/01/05-08:01am
X Xr 10/01/05-08:00am
X Xr 10/01/05-08:01am


--
xtrmhyper
------------------------------------------------------------------------
xtrmhyper's Profile: http://www.excelforum.com/member.php...o&userid=23851
View this thread: http://www.excelforum.com/showthread...hreadid=478152

count unique records with latest date
 

Hello xtrmhyper,

This macro will return a Variant Array. Array(0) = the number of unique
entries and Array(n) to Array(n+x) = the column "B" values sorted in
ascending order. Just put the sort range in as a string. The range is
assumed to be on the active worksheet and to contain 3 columns.

CALLING THE MACRO:

Dim N, X

N = FindUnique("A1:C4")

X = N(0) ' X = 2
X = N(1) ' X = "Ed"
X = N(2) ' X = Xr


MACRO CODE:


Code:
--------------------
Public Function FindUnque(ByVal Sort_Range As String)

Dim Col As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim vArray()

ReDim vArray(0)

With ActiveSheet.Range(Sort_Range)
Col = .Column + 1
FirstRow = .Row
LastRow = .Rows.Count + FirstRow - 1
End With

For I = FirstRow To LastRow
If Cells(I, Col).Value < Cells(I + 1, Col).Value Then
N = N + 1
ReDim Preserve vArray(N)
vArray(N) = Cells(I, Col).Value
End If
Next I

vArray(0) = N
Test = vArray

End Function

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=478152

count unique records with latest date
 

Try this formula

=COUNT(1/FREQUENCY(IF((A2:A10="X")*($C$2:$C$10=MAX($C$2:$C$ 10)),MATCH(B2:B10
,B2:B10,0)),ROW(INDIRECT("2:"&ROWS(B2:B10)-ROW(B2)+1))))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"xtrmhyper" wrote
in message ...

I Want To Count All "x" With Unique Record In Column B
Parameter Must Be The Latest Date In Colum C Will Count

Answer Is "2"
Date Of 10/01/05 08:01am
Ed & Xr

What Is The Easyway To Create This Formula
Tnx


A B C


X Ed 10/01/05-08:00am
X Ed 10/01/05-08:01am
X Xr 10/01/05-08:00am
X Xr 10/01/05-08:01am


--
xtrmhyper
------------------------------------------------------------------------
xtrmhyper's Profile:
http://www.excelforum.com/member.php...o&userid=23851
View this thread: http://www.excelforum.com/showthread...hreadid=478152