Can be done live with formulas, but requires several steps. Use the Function
Wizard or Help to check out the functions I use if you're not familiar with
them.

I'm assuming that your department names are in A11:A20, and the number of
people in each in B11:B20. The following formuals are as entered in row 11
and will normally be copied into rows 12 - 20 unless otherwise specified.

C11 = RANK(B11,B$11:B$20) - returns descending rank order, but same numbers
will have same rank.

Therefore, Column D has an Adjusted Rank, so that all ranks are unique:
D11 = C11 - no adjustment to the top row.
D12 = C12 + COUNTIF(C$11:C11,c12) - copied down to D20, increases the rank
by 1 for each value of the same raw rank in the rows above.

E11:E20 contains the numbers 1 to 10 entered as data, being the Ranks in the
order that you want them displayed

Column F finds the row in the original data where the required rank exists.
F11 = MATCH(E11,D$11:D$20,0)
If you didn't do the adjustment in column D but rather used the raw ranks in
column C, the MATCH() would return #N/A errors for duplicate ranks.

Column G contains a TRUE/FALSE Flag to indicate whether the ranked item has
people and is to be displayed or not
G11 =INDEX(B$11:B$20,F11)0

Columns H & I contain the sorted data:
H11 = IF($G11,INDEX(A$11:A$20,$F11),"") and copied to H11:I20

Good luck

BrianH


"Air_Step" wrote:

I have a spreadsheet that counts the number of times certain things appear on
another page and list them all out together. What I end up with is a list of
names (all the departments in the company) and the number of persons who are
currently "in" that department.

It looks like this

Medical 0
Finance 2
Admin 3
Car Wash 0
Publications 0
Reception 4
Maintenance 1
IT 2

Etc.

What I need is to have the list automatically trimmed to remove those lines
that don't have a number against them, and sort them into number order. So
the above would look like this:

Reception 4
Admin 3
Finance 2
IT 2
Maintenance 1

This is fairly easy to arrange 'manually' - ie sorting the list, but can I
get it to occur automatically, as the original list is itself being updated
from another data source?

Thanks in advance for any assistance.

Air_Step