Jun 23, 2011
A would like to create a summary of some data:
Column 1 Has group IDs (~5000 groups with 11 members each)
Column B has the individual IDs for each member - creatively labelled 1-11)
Column III has a numeric value for each of the members
I would like to create an analysis that shows how many times each member (1-11) was either the minimum or maximum value of the group
For example of the 5000 groups
Member 1 was the maximum 1213 times of the 5000 groups
Member 2 was 1001 times
How would you do this most efficiently?
You'll need to create a column that identifies a row as the minimum (or maximum) within the Group.
This formula will do that:
Col Minimum(:Measure, :Group) == :Measure
Giving a 1 or 0 in each row. The 1s are the rows where that member was the minimum within Group.
Then you can use Tabulate to get a sum of this new column for each Member ID.
Is Min of Group
The attached data table shows the technique and has an attached script for the Tabulate.