count unique records with latest date

[xtrmhyper]

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

 

[Leith Ross]

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

 

[Bob Phillips]

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)