Finding top n most frequent - 2 subtley different problems

[ali]

Hi folks,

i have a range of data

column a: staff payroll number
column b: first date of absence
column c last date of absence
column d: number of days absent (as some staff are part-time)

the 4 pieces of data are added succesively when staff return to work
folowing a period of absence (staff can appear multiple times)

I need to:

a) find the top N staff who're most frequently absent and,
b) find the top N staff who've racked up the most days absence.

Ideally I'd like to produce these lists within the date parameters, i.e. for
April 1st to June 30th


thanks in advance
paul

 

[smartin]

Hi folks,

i have a range of data

column a: staff payroll number
column b: first date of absence
column c last date of absence
column d: number of days absent (as some staff are part-time)

the 4 pieces of data are added succesively when staff return to work
folowing a period of absence (staff can appear multiple times)

I need to:

a) find the top N staff who're most frequently absent and,
b) find the top N staff who've racked up the most days absence.

Ideally I'd like to produce these lists within the date parameters, i.e.
for April 1st to June 30th


thanks in advance
paul

How about a pivot table? You can set it to show top N based on (a) count
of staff payroll number or (b) sum of number of days absent. Also by
adding a column to group dates you can determine frequency within that
category.