Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort many rows independently?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort many rows independently?
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 :D) but I'm a novice! Cheers, Jef -- guillemo ----------------------------------------------------------------------- guillemot's Profile: http://www.excelforum.com/member.php...fo&userid=2983 View this thread: http://www.excelforum.com/showthread.php?threadid=49574 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort many rows independently?
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort many rows independently?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort many rows independently?
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort many rows independently?
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 ------------------------------------------------------------------------ guillemot's Profile: http://www.excelforum.com/member.php...o&userid=29833 View this thread: http://www.excelforum.com/showthread...hreadid=495747 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort many rows independently?
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 ------------------------------------------------------------------------ guillemot's Profile: http://www.excelforum.com/member.php...o&userid=29833 View this thread: http://www.excelforum.com/showthread...hreadid=495747 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort many rows independently?
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. -- guillemot ------------------------------------------------------------------------ guillemot's Profile: http://www.excelforum.com/member.php...o&userid=29833 View this thread: http://www.excelforum.com/showthread...hreadid=495747 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering columns independently | Excel Discussion (Misc queries) | |||
Sort by color: Is there an easy way to sort columns or rows in EX | Excel Worksheet Functions | |||
why does the highlighter independently jump to another cell | Excel Discussion (Misc queries) | |||
How can I set up Excel to auto-sort each column independently? | Excel Worksheet Functions | |||
Sorting data rows independently | Excel Discussion (Misc queries) |