PivotTable/Table to show Daily Max or Min Value

[davidlim]

hi guys,


here's a problem i've been trying to solve in Pivottable or Excel Table.


assuming i have this list:


date number

26.07.2011 1

26.07.2011 23

26.07.2011 4

26.07.2011 65

26.07.2011 31

26.07.2011 23

27.07.2011 3

27.07.2011 2

27.07.2011 4

27.07.2011 1

28.07.2011 23

28.07.2011 3

28.07.2011 1

28.07.2011 5

28.07.2011 8


i've been trying to filter the list to only show the daily (each day) max or min value.


the target values should show (daily max):


26.07.2011 65

27.07.2011 4

28.07.2011 23


one thing to note is this list is about 300K rows (and still adding); where each day have about 10k items.


Is there a way (via Pivottable/Table/or ??) to extract the max/min value for each date?


thanks!

 

[Hui]

David


I'd suggest using the DMin and DMax functions


With your data in A1:B1000


use something like

=DMin(A1:B16,"Number",D1:D2)

and

=DMax(A1:B16,"Number",D1:D2)


where you have

Date

26/7/2011

as D1:D2

etc