how to create a sorted summary list of counted species

[kellerp.l]

Here's what I have:

An array of bird counts. 4 (yearly) sheets with bird counts. The
first column is the list of species, the second column is the sum of
the remaining columns, the successive columns are the counts for each
species for that day.

So,

species,sum,number,number,number,...

What I want to do, in a separate sheet, is to create a table of all the
species that have totals within a range.

Basically I want to end up with:

1000 >900 >800 >700 >600 >50

species species species species species species
species species species species
species

The above numbers are just an example. It would really be an added
bonus if the lists of species in a range were sorted by their
abundance.

Any ideas as to how to go about this?

 

[Bryan Hessey]

Excluding VB code:

Copy columns A & B and, to a new sheet, Paste Special, Values

In cell C2 the formula


=IF(B1>1000,1000,IF(B1>900,900,IF(B1>800,800,IF(B1>700,700,IF(B1>600,600,IF(B1>50,50,1))))))

and formula copy to the end of your data.

Sort over column C descending, then B (for biggest on left, or A for
alpabetical species within group)

In cell D2 put

=IF(C2=C1,"","sort")

and formula copy down to the end of your data

in cell E2 put


=IF($C2=$C1,"",IF(INDIRECT("$C"&ROW()+COLUMN()-4)=$C2,INDIRECT("$A"&ROW()+COLUMN()-4),""))

and formula copy this as far right as required (a column for each
possible species) then bulk-formula copy E2 to (say) Z2 down as far as
your data goes.

(save your sheet)

Select all data (click the cell above A and left of 1) and Copy,
PasteSpecial, Values back onto itsself.
Sort over column D and delete any line that does not contain the word
'sort'
Sort over column C descending.

Obviously if you need to do this often you should look to some VB code,
perhaps record a macro whilst doing the above to give a good start.

Hope this helps

--