software

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 quot;inquot; 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

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

HTH
--
AP

quot;Air_Stepquot; gt; a écrit dans le message de
...
gt; I have a spreadsheet that counts the number of times certain things appear
on
gt; another page and list them all out together. What I end up with is a list
of
gt; names (all the departments in the company) and the number of persons who
are
gt; currently quot;inquot; that department.
gt;
gt; It looks like this
gt;
gt; Medical 0
gt; Finance 2
gt; Admin 3
gt; Car Wash 0
gt; Publications 0
gt; Reception 4
gt; Maintenance 1
gt; IT 2
gt;
gt; Etc.
gt;
gt; What I need is to have the list automatically trimmed to remove those
lines
gt; that don't have a number against them, and sort them into number order.
So
gt; the above would look like this:
gt;
gt; Reception 4
gt; Admin 3
gt; Finance 2
gt; IT 2
gt; Maintenance 1
gt;
gt; This is fairly easy to arrange 'manually' - ie sorting the list, but can I
gt; get it to occur automatically, as the original list is itself being
updated
gt; from another data source?
gt;
gt; Thanks in advance for any assistance.
gt;
gt; Air_Step
Here's a non-array formulas play
which delivers exactly what you want

Sample construct available at:
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())),quot;quot;,
INDEX(X!A:A,MATCH(LARGE($C:$C,ROW()),$C:$C,0)))
Copy A1 to B1

Put in C1: =IF(X!B1lt;=0,quot;quot;,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.:

gt; Reception 4
gt; Admin 3
gt; Finance 2
gt; IT 2
gt; Maintenance 1

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Air_Stepquot; gt; wrote in message
...
gt; I have a spreadsheet that counts the number of times certain things appear
on
gt; another page and list them all out together. What I end up with is a list
of
gt; names (all the departments in the company) and the number of persons who
are
gt; currently quot;inquot; that department.
gt;
gt; It looks like this
gt;
gt; Medical 0
gt; Finance 2
gt; Admin 3
gt; Car Wash 0
gt; Publications 0
gt; Reception 4
gt; Maintenance 1
gt; IT 2
gt;
gt; Etc.
gt;
gt; What I need is to have the list automatically trimmed to remove those
lines
gt; that don't have a number against them, and sort them into number order.
So
gt; the above would look like this:
gt;
gt; Reception 4
gt; Admin 3
gt; Finance 2
gt; IT 2
gt; Maintenance 1
gt;
gt; This is fairly easy to arrange 'manually' - ie sorting the list, but can I
gt; get it to occur automatically, as the original list is itself being
updated
gt; from another data source?
gt;
gt; Thanks in advance for any assistance.
gt;
gt; Air_Step
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)gt;0

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

Good luck

BrianHquot;Air_Stepquot; wrote:

gt; I have a spreadsheet that counts the number of times certain things appear on
gt; another page and list them all out together. What I end up with is a list of
gt; names (all the departments in the company) and the number of persons who are
gt; currently quot;inquot; that department.
gt;
gt; It looks like this
gt;
gt; Medical 0
gt; Finance 2
gt; Admin 3
gt; Car Wash 0
gt; Publications 0
gt; Reception 4
gt; Maintenance 1
gt; IT 2
gt;
gt; Etc.
gt;
gt; What I need is to have the list automatically trimmed to remove those lines
gt; that don't have a number against them, and sort them into number order. So
gt; the above would look like this:
gt;
gt; Reception 4
gt; Admin 3
gt; Finance 2
gt; IT 2
gt; Maintenance 1
gt;
gt; This is fairly easy to arrange 'manually' - ie sorting the list, but can I
gt; get it to occur automatically, as the original list is itself being updated
gt; from another data source?
gt;
gt; Thanks in advance for any assistance.
gt;
gt; Air_Step