I have data stored in excel where I need to sort that data

Question

In excel, I have data divided into

Year    Code        Class   Count
2001    RAI01       LNS      9
2001    RAI01       APRP     4
2001    RAI01                3
2002    RAI01       BPR      3
2002    RAI01       BRK      3
2003    RAI01       URE      3
2003    CFCOLLTXFT  APRP     2
2003    CFCOLLTXFT  BPR      2
2004    CFCOLLTXFT  GRL      2
2004    CFCOLLTXFT  HDS      2
2005    RAI         HDS      2

where I need to find the top 3 products for that particular customer for that particular year.

Answer 1

The real trick here is to rank each row based on a group.

Your rank is determined by your Count column (Column D).

Your group is determined by your Year and Code (I think) columns (Column A and B respectively).

You can use this gnarly sumproduct() formula to get a rank (Starting at 1) based on the Count for each Group.

So to get a ranking for each Year and Code from 1 to whatever, in a new column next to this data:

=SUMPRODUCT(($A$2:$A$50=A2)*(B2=$B$2:$B$50)*(D2<$D$2:$D$50))+1

And copy that down. Now you can AutoFilter on this to show all rows that have a rank less than 4. You can sort this on Customer, then Year and you should have a nice list of top 3 within each year/code.

---

Explanation of sumproduct.

Sumproduct goes row by row and applies the math that is defined for each row. When it is done it sums the results.

As an example, take the following worksheet:

+---+---+---+
|   | A | B |
+---+---+---+
| 1 | 1 | 1 |
| 2 | 1 | 4 |
| 3 | 2 | 2 |
| 4 | 4 | 1 |
| 5 | 1 | 2 |
+---+---+---+

 `=SUMPRODUCT((A1:A5)*(B1:B5))` 

This sumproduct will take A1*B1, A2*B2, A3*B3, A4*B4, A5*B5 and then add those five results up to give you a number. That is 1 + 4 + 4 + 4 + 1 = 15

It will also work on conditional/boolean statements returning, for each row/condition a 1 or a 0 (for True and False, which is a "Boolean" value).

As an example, take the following worksheet that holds the type of publication in a library and a count:

+---+----------+---+
|   |    A     | B |
+---+----------+---+
| 1 | Book     | 1 |
| 2 | Magazine | 4 |
| 3 | Book     | 2 |
| 4 | Comic    | 1 |
| 5 | Pamphlet | 2 |
+---+----------+---+

=SUMPRODUCT((A1:A5="Book")*(B1:B5))

This will test to see if A1 is "Book" and return a 1 or 0 then multiple that result by whatever is B1. Then continue for each row in the range up to row 5. The result will 1+0+2+0+0 = 3. There are 3 books in the library (it's not a very big library).

For this answer's sumproduct:

So ($A$2:$A$50=A2) says to return a 1 if A2=A2 or a 0 if A2<>A2. It does that for A2 through A50 comparing it to A2, returning a 1 or a 0.

(B2=$B$2:$B$50) will test each cell B2 through B50 to see if it is equal to B2 and return a 1 or 0 for each test.

The same is true for (D2<$D$2:$D$50) but it's testing to see if the count is less than the current cells count.

So... essentially this is saying "For all the rows 1 through 50, test to find all the other rows that have the same value in Column A and B AND have a count less than this rows count. Count all of those rows up that meet that criteria, and add 1 to it. This is the rank of this row within its group."

Copying this formula has it redetermine that rank for each row allowing you to rank and filter.