how to sort many rows independently?

[guillemot]

Repost from Misc. forum. Maybe it belongs over here instead:

Hello all,

I have a large dataset in excel that I need to sort and rank b
abundance. It includes information on the abundances of each of 15
different plant species at each of eight locations, during si
different sampling periods (3 years, fall and spring) with twent
replicates per site (plot number). There are separate site, season
year, and plot columns to identify individual records, as well as on
column for each species.

I need to create rank-abundance profiles for each replicate site durin
each sampling period. For further clarification on what these are, do
google search for "species rank abundance plot". There is an image tha
comes up and many web pages that explain the concept. To do this, I nee
to sort the species data in each row by rank-within-row from mos
abundant to least abundant from left to right. There will be a lot o
zeros on the right since most plots only have 5 - 20 of the 154 specie
in them. The abundance values don't need to be linked with the specie
name headers anymore. I hope that part was clear.

Then I want to calculate the proportion of of the row total abundanc
that each species contributes. Then (it goes on), I want to calculat
mean rank-abundance profiles for each site by sampling dat
combinations by averaging across the 20 replicate plots. I want to d
this part twice - once with the proportion data, and once with the ra
data. Here's how the data is organized. There are 960 rows:

Site Season Year Plot# Species1 Sp2 Sp3 Sp4.... Sp154
A Spring 2003 1 0 5 2 15
A Spring 2003 2 5 0 1 3

 

[Tom Ogilvy]

turn on the macro recorder

select a single row or subset of a row

Select Sort, in the sort dialog, select the options button and select left
to right

finish the sort.

Turn off the macro recorder.

then it will be something like

set rng = selection
for each cell in rng.columns(1).Cells
cell.resize(1,20).sort - rest of recorded code
Next

 

[guillemot]

Tom,

Thanks for replying. I have very limited programming experience (in an
language) and VB is the language that I'm least familiar with. Can yo
help clarify what you've written for a beginner?

I recorded the macro as you instructed. Then I opend the VB editor
Here's what the sub looks like:

Sub SortSpecies()
'
' SortSpecies Macro
' Macro recorded 12/23/2005 by T-Dog
'

'
Range("F2:FD2").Select
Selection.Sort Key1:=Range("F2"), Order1:=xlDescending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
_
DataOption1:=xlSortNormal
End Sub

How do I get it to do this for all rows that follow? Sorry if this i
really basic (pun intended ) but I'm a novice!

Cheers,
Jef

 

[Tom Ogilvy]

Sub SortSpecies()
Dim rng as Range, cell as Range
set rng = Range(Range("F2"),Range("F2").end(xldown))
for each cell in rng
cell.Resize(1,155).Sort Key1:=cell, _
Order1:=xlDescending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Next
End Sub

 

[guillemot]

Excellent! Man, that would have taken forever!

OK, another layer of complexity. Is it possible to do that type of sort
and still have the sorted values tied to the column headers that are in
row 1 so that I can see the names of the species in order of abundance?


Perhaps if it repeated the sort on an additional worksheet I could pull
the two sheets into my access database and link them.

Thanks again,
Jeff

 

[Tom Ogilvy]

If you sort each row independently, are you suggesting adding a row and
bringing down the species header for each row, so if you had 100 rows, now
you would have 200 with a species header row preceding each data row.

If not, then what are you suggesting?

 

[guillemot]

Not quite like that. I am think of a duplicate worksheet that mirrors
the content of the original (that your macro sorts), but instead of
listing the numerical data, it would list the header data from row one
- the species names. If the numerical and categorical data become
interspersed they become difficult to manipulate. Keeping them on
separate worksheets would be best.

 

[guillemot]

Actually, I think that your (Tom's) idea will work as long as the
columns with the site/date data are reproduced with the new rows. I
could just sort the rows afterwards and copy out the relevant ones. How
can this be done?

 

[guillemot]

I have the data sorted now, but I've run into another two stumbling
blocks. I want to calculate averages for subgroups of rows.

I changed the column headers to species rank numbers and deleted the
extra colmns (of 155 species, the most seen in any given plot was just
17). I want to calculate the average of each rank category (1 - 17) for
each site x season combination. Pivot table wants to make my columns
into rows. It also doesn't put the site and seaon data in each row. It
has the site name on one row and then all rows from that site follow,
but don't have the right data. I need the data to look like this for
importation into SYSTAT:

Site Season Year Rank Abundance
Site_A Spring 2003 1 35%
Site_A Spring 2003 2 25%
Site_A Spring 2003 3 5%
Site_A Fall 2003 1 45%
Site_A Fall 2003 2 35%

Where the abundance is the mean for that particular rank x season x
year combination.