Filtering Data to find the Top (X):

In the list below, consisting of the “Date”, “Vendor” and “Sold Items” columns, we want the top X vendors (in this example, the top 3) for the day.

In cell D1, we type “Top X” and then in cell D2, we type the formula: = IF (A2 = A1, D1 +1, 1) and press CTRL + ENTER. Then copy the formula downwards.

Click in a cell that contains data (any cell is fine), click on the “Data” (1) tab in the “Ribbon” and select “Filter” (2).

The column headers now have filter buttons (3).

We first sort the “Sold Items” from top to bottom. Click the arrow pointing downwards (4) on “Sold Items” and select “Sort Descending” (5).

After this action, we have the “Sold items”, sorted from high to low but, the “Dates” are not sorted.
So, we have to sort the “Dates” from old to new (it may also be from new to old).

The result of this sort action gives the following division: the “Dates” are sorted from old to new (7) and then the “Sold items” are sorted from high to low (8).

Now, to get the display of the top X “Vendors” per day (in this example we consider the top 3), we click on the arrow pointing downwards on the Top X (9) column and in the list, we uncheck the “Select all” check box and then we select the numbers 1, 2 and 3 (10) check boxes and click OK.

The list of the top 3 best selling “Vendors” per day (11) is displayed.

FYI: Swotster.com thinks of its students, so you also remember swotster.com.

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats