How to filter date-wise highest value [Closed]

Report
Posts
1
Registration date
Friday May 16, 2014
Status
Member
Last seen
May 16, 2014
-
 RayH -
i have a data with two columns.

first column is date and second column is amount
in first column same date will be repeated for more number of times and this repetation of each date vary from date to date. say 1-jan-2013 may have 10 rows and 2-jan-2013 may have 15 rows.

what i want is largest amount on each date.

please give me reply as early as possible

1 reply

Take a look at this as it may help you and others with this:
Date	Number	AAA		BBB	CCC
1/3/2014 99 1/3/2014 6 99
1/2/2014 79 1/2/2014 6 93
1/3/2014 40 1/1/2014 4 100
1/3/2014 64 0
1/2/2014 71 0
1/3/2014 78 0
1/3/2014 18 0
1/2/2014 67 0
1/3/2014 72 0
1/2/2014 93 0
1/2/2014 9 0
1/1/2014 100 0
1/1/2014 6 0
1/1/2014 81 0
1/1/2014 97 0
1/2/2014 11 0

AAA - Distinct list of Dates
{=IFNA(INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$17),0,0),0)),"")}

BBB - Count of Distinct Dates in list
{=COUNTIF(A2:A17,INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$17),0,0),0)))}
Note:
This will produce Zeros that could be hidden using an IF function but is not included here for clarity


CCC -
MAX value of Number field against distinct Date
{=IFNA(MAX(IF(A:A=INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$17),0,0),0)),B:B)),"")}

These are all ARRAY formulas and are entered with CTRL-SHIFT-Enter
Do not type in the { or } symbols these will automatically be produced after pressing Ctrl-Shift-Enter

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!