Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Records | Excel Worksheet Functions | |||
count unique records | Excel Worksheet Functions | |||
Count Unique Records | New Users to Excel | |||
Count Unique Records | Excel Worksheet Functions | |||
code not unique find latest date | Excel Discussion (Misc queries) |