DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)
Features | Testimonial | Download free 15-day trial | Purchase | FAQ | Contact

Filter Maximum (max), Minimum (min)

This filter allow you to extract the rows in a table that have max or min values in sub groups. For example, I have a list of customer transactions, how do I find the
  1. Largest (maximum in 'Purchase') transaction records of each 'Customer'
  2. Latest (maximum in 'Date') transaction records of each 'Customer'
  3. Smallest (minimum in 'Purchase') transaction records of each 'Date'

    Filter Maximum (max), Minimum (min)
    Practice file - filter-max-min-demo.xls (16k)

Note that this is different from using DigDB roll-up or Excel's pivot table or subtotal to get the max or min values by sub groups. Those function caculate the roll-ups and output result to a new table. In our case, we need to filter within the current table to get the rows with the max and min values.

Filter the latest transactions by each customer

In other words, I need to filter the rows that have the max value in 'Purchase' in each 'Customer' sub group.
  1. Sort the list by 'Customer', then by 'Date' - you cannot sort the 2 columns separately, instead, you need to use DigDB's Multi-level Sort:

    Filter Maximum (max), Minimum (min)

    Click 'Then by' to add more columns to sort by.

    Filter Maximum (max), Minimum (min)

    Filter Maximum (max), Minimum (min)

    Sorting done. Now, the rows of each 'customer' are sorted by 'Date', from latest to oldest

    Filter Maximum (max), Minimum (min)

  2. Invoke 'DigDB->Complex Filter->Filter Duplicates...'

    Filter Maximum (max), Minimum (min)

    Set how to filter

    Filter Maximum (max), Minimum (min)

    Only the first row of each 'Customer' is shown, which is already sorted to be the latest 'Date' (max)

    Filter Maximum (max), Minimum (min)

    Extract the filter result

    Filter Maximum (max), Minimum (min)

    The filter result will be copied to a new sheet.

Need more?

Features | Testimonial | Download | Purchase | FAQ | Contact

Copyright © 1999-2007 All rights reserved

DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft Office Marketplace logo
Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.