Rank a list with groupings

_dateter

I want to rank a long list of records that contains groups and sub-groupings. The ranking would be based on each sub-group (e.g. 'Top 3 Products Purchased per Customer'). I think this should be fairly straight-forward but can't figure out how to do it. The data has three variables: Customer ID, Product Name, Number of Products Purchased. So for each unique customer id I need to sort and rank their top 3 products based on number of purchases. 

Input:
Customer ID / Product / Num
12345 / Widget 1 / 999
12345 / Widget 2 / 750
12345 / Widget 3 / 500
12346 / Widget 1 / 99
12346 / Widget 4 / 25
12347 / Widget 4 / 56
12347 / Widget 5 / 25

Desired Output:
Customer ID / Product / Num / Rank
12345 / Widget 1 / 999 / 1
12345 / Widget 2 / 750 / 2
12345 / Widget 3 / 500 / 3
12346 / Widget 1 / 99   / 1
12346 / Widget 4 / 25   / 2
12347 / Widget 4 / 56   / 1
12347 / Widget 5 / 25   / 2

tom_montpool

I can think of two ways to accomplish this. I will assume here that the data have been sorted already.

1. 
   Formula & Running Total
   • 
     Use a Formula tool to add a field filled with 1s (e.g. Rank)
   • 
     Then use the Running Total tool configured to group by your Customer ID and to create the Running Total of the [Rank] field.
2. 
   Multi-Row Formula
   • 
     Use a Multi-Row Formula tool to create a new field called Rank using the expression "IF [Customer ID]==[Row-1:Customer ID] THEN [Row-1:Rank]+1 ELSE 1 ENDIF".