AlanB
10-14-2018, 01:37 PM
Hello - I was wondering if anyone can help as I am struggling with a problem after trying on my own and searching for a solution online.
I have various excel documents that I already have and will be regularly creating that I would like to sort some of the headed columns of each row numerically from high to low. In each document the data is always stored in sheet 1. The sheets can have anything from 100 to 40k rows - with each row to be sorted.
Columns A to T in each row each contain text and numbers that can be ignored but the data in columns U To AC is what I would like to rearrange/ sort from high to low (highest in column U lowest in column AC). There is also text in columns after AC that can be ignored.
The data in the cells that I want to rearrange into numerical order has both text and a number (e.g. Cat;20)
I do not want to delete the text during the sort or rearrange the string.
The number always follows a ;
Numbers have no relationship to the word
Some numbers might be the same - e.g. tiger and dog or horse and hamster in the example below- if number match I do not mind the order order but I guess alphabetically would be best if possible.
E.g. for one of the rows before VBA
Row 2 Column U (Header = Skills) Cat;20
Row 2 Column V (Header = Skills 2) Dog;30
Row 2 Column W (Header = Skills 3) Snake;99
Row 2 Column X (Header = Skills 4) Horse;5
Row 2 Column Y (Header = Skills 5) Hamster;5
Row 2 Column Z (Header = Skills 6) Rabbit;10
Row 2 Column AA (Header = Skills 7) Rat;15
Row 2 Column AB (Header = Skills 8) Lion;67
Row 2 Column AC (Header = Skills 9) Tiger;30
E.g. for the same row after VBA has been applied.
Row 2 Column U (Header = Skills) Snake;99
Row 2 Column V (Header = Skills 2) Lion;67
Row 2 Column W (Header = Skills 3) Dog;30
Row 2 Column X (Header = Skills 4) Tiger;30
Row 2 Column Y (Header = Skills 5) Cat;20
Row 2 Column Z (Header = Skills 6) Rat;15
Row 2 Column AA (Header = Skills 7) Rabbit;10
Row 2 Column AB (Header = Skills 8) Hamster;5
Row 2 Column AC (Header = Skills 9) Horse;5
The cells in the other columns indicated above are to be ignored and not moved.
Notes
The cells that I would like to rearrange might change in future but they will always have the same headers Skills, Skills 2, Skills 3, Skills 4 up to Skills 9 as in example above.
Some of the rows only have text and no numbers within the cells that are to be sorted. However it is consistent - all cells have just text or all cells have text and number. These rows could either be ignored as I do not mind how these rows are sorted.
3.
Not vital but if possible (others will be using this) - before the vba is applied can a input box be created for the name of the sheet to be reorganized to be manually inputted. And also a new sheet created for the results. So essentially after VBA is applied there are now two sheets - a before and after
Many thanks in advance for anyone who has advice or can help as I am struggling!
Alan
I have various excel documents that I already have and will be regularly creating that I would like to sort some of the headed columns of each row numerically from high to low. In each document the data is always stored in sheet 1. The sheets can have anything from 100 to 40k rows - with each row to be sorted.
Columns A to T in each row each contain text and numbers that can be ignored but the data in columns U To AC is what I would like to rearrange/ sort from high to low (highest in column U lowest in column AC). There is also text in columns after AC that can be ignored.
The data in the cells that I want to rearrange into numerical order has both text and a number (e.g. Cat;20)
I do not want to delete the text during the sort or rearrange the string.
The number always follows a ;
Numbers have no relationship to the word
Some numbers might be the same - e.g. tiger and dog or horse and hamster in the example below- if number match I do not mind the order order but I guess alphabetically would be best if possible.
E.g. for one of the rows before VBA
Row 2 Column U (Header = Skills) Cat;20
Row 2 Column V (Header = Skills 2) Dog;30
Row 2 Column W (Header = Skills 3) Snake;99
Row 2 Column X (Header = Skills 4) Horse;5
Row 2 Column Y (Header = Skills 5) Hamster;5
Row 2 Column Z (Header = Skills 6) Rabbit;10
Row 2 Column AA (Header = Skills 7) Rat;15
Row 2 Column AB (Header = Skills 8) Lion;67
Row 2 Column AC (Header = Skills 9) Tiger;30
E.g. for the same row after VBA has been applied.
Row 2 Column U (Header = Skills) Snake;99
Row 2 Column V (Header = Skills 2) Lion;67
Row 2 Column W (Header = Skills 3) Dog;30
Row 2 Column X (Header = Skills 4) Tiger;30
Row 2 Column Y (Header = Skills 5) Cat;20
Row 2 Column Z (Header = Skills 6) Rat;15
Row 2 Column AA (Header = Skills 7) Rabbit;10
Row 2 Column AB (Header = Skills 8) Hamster;5
Row 2 Column AC (Header = Skills 9) Horse;5
The cells in the other columns indicated above are to be ignored and not moved.
Notes
The cells that I would like to rearrange might change in future but they will always have the same headers Skills, Skills 2, Skills 3, Skills 4 up to Skills 9 as in example above.
Some of the rows only have text and no numbers within the cells that are to be sorted. However it is consistent - all cells have just text or all cells have text and number. These rows could either be ignored as I do not mind how these rows are sorted.
3.
Not vital but if possible (others will be using this) - before the vba is applied can a input box be created for the name of the sheet to be reorganized to be manually inputted. And also a new sheet created for the results. So essentially after VBA is applied there are now two sheets - a before and after
Many thanks in advance for anyone who has advice or can help as I am struggling!
Alan