how to sort many rows independently?

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 by abundance. It includes information on the abundances of each of 154 different plant species at each of eight locations, during six different sampling periods (3 years, fall and spring) with twenty replicates per site (plot number). There are separate site, season, year, and plot columns to identify individual records, as well as one column for each species.

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

Then I want to calculate the proportion of of the row total abundance that each species contributes. Then (it goes on), I want to calculate mean rank-abundance profiles for each site by sampling date combinations by averaging across the 20 replicate plots. I want to do this part twice - once with the proportion data, and once with the raw 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
.
.
A Spring 2003 20 8 5 9 10
B Spring 2003 1 0 5 2 15
B Spring 2003 2 0 5 2 15
.
.
B Spring 2003 20 3 2 22 15

I want to sort it like this:

Site Season Year Plot# Species1 Sp2 Sp3 Sp4....
A Spring 2003 1 15 5 2
A Spring 2003 1 5 3 2
.
.
A Spring 2003 20 10 9 8 5
B Spring 2003 1 15 5 2
B Spring 2003 2 15 5 2
.
.
B Spring 2003 20 22 15 3 2
etc...

and then calculate proportions for each record (row) and average for each site by season. The species column headers no longer reffer to the actual species names (like red maple and such). They are the rank numbers of the sorted data.

Example of mean profile:
Site Season Year Sp1 Sp2 Sp3 Sp4
A Spring 2003 10 5.6 4 1.6
B Spring 2003 .....

How can I get excel to sort my data rows independently of each other simultaneousely (or in sequence)? The sort function always links the data together, either by rows or columns depending on which way you sort. Is it easy to then calculate the proportion information and means automatically? That could happen on a separate worksheet. Many thanks in advance for your advice!

Cheers,
Jeff

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

--
Regards,
Tom Ogilvy


"guillemot" <[email protected]> wrote
in message news:[email protected]...
>
> 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 by
> abundance. It includes information on the abundances of each of 154
> different plant species at each of eight locations, during six
> different sampling periods (3 years, fall and spring) with twenty
> replicates per site (plot number). There are separate site, season,
> year, and plot columns to identify individual records, as well as one
> column for each species.
>
> I need to create rank-abundance profiles for each replicate site during
> each sampling period. For further clarification on what these are, do a
> google search for "species rank abundance plot". There is an image that
> comes up and many web pages that explain the concept. To do this, I need
> to sort the species data in each row by rank-within-row from most
> abundant to least abundant from left to right. There will be a lot of
> zeros on the right since most plots only have 5 - 20 of the 154 species
> in them. The abundance values don't need to be linked with the species
> name headers anymore. I hope that part was clear.
>
> Then I want to calculate the proportion of of the row total abundance
> that each species contributes. Then (it goes on), I want to calculate
> mean rank-abundance profiles for each site by sampling date
> combinations by averaging across the 20 replicate plots. I want to do
> this part twice - once with the proportion data, and once with the raw
> 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

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

--
Regards,
Tom Ogilvy



"guillemot" <[email protected]> wrote
in message news:[email protected]...
>
> Tom,
>
> Thanks for replying. I have very limited programming experience (in any
> language) and VB is the language that I'm least familiar with. Can you
> 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 is
> really basic (pun intended :D) but I'm a novice!
>
> Cheers,
> Jeff
>
>
> --
> guillemot
> ------------------------------------------------------------------------
> guillemot's Profile:
http://www.excelforum.com/member.php...o&userid=29833
> View this thread: http://www.excelforum.com/showthread...hreadid=495747
>

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

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?

--
Regards,
Tom Ogilvy

"guillemot" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> guillemot
> ------------------------------------------------------------------------
> guillemot's Profile:
http://www.excelforum.com/member.php...o&userid=29833
> View this thread: http://www.excelforum.com/showthread...hreadid=495747
>

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?

--
Regards,
Tom Ogilvy

"guillemot" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> guillemot
> ------------------------------------------------------------------------
> guillemot's Profile:
http://www.excelforum.com/member.php...o&userid=29833
> View this thread: http://www.excelforum.com/showthread...hreadid=495747
>

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.

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?

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.