How to choose from a list that includes blanks?

[Guest]

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

 

[Ardus Petus]

To keep your data automatically sorted when changes occur, you will need a
VBA macro.

HTH

 

[Max]

Here's a non-array formulas play
which delivers exactly what you want

Sample construct available at:
http://www.savefile.com/files/4442286
Auto-Filter n Sort in Desc order in another sheet.xls

Assume source data is in sheet: X,
cols A and B, from row1 down

In another sheet: Y,

Put in A1:
=IF(ISERROR(LARGE($C:$C,ROW())),"",
INDEX(X!A:A,MATCH(LARGE($C:$C,ROW()),$C:$C,0)))
Copy A1 to B1

Put in C1: =IF(X!B1<=0,"",X!B1-ROW()/10^10)

Select A1:C1, fill down to say, C10
to cover the max expected extent of data in X

Sheet: Y will auto-return the desired results from X,
with all lines neatly bunched at the top, viz.:

Reception 4
Admin 3
Finance 2
IT 2
Maintenance 1

---

 

[Guest]

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$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