I'm not sure if this is possible. Does anyone know of a way to maintain two lists of data where items are added to to random locations within the first one and the second one always lists all items in alphabetical order ? (Without having to run anything to do it; i.e. with formulas and/or functions). Assume up to 300 items. There may be gaps of several rows of spaces in the first list, so it would have to be defined as going from row 1 to row 300, rather than by contiguous data. Thank you for any help.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Ongoing alphabetized list
- Thread starter Sneech#7
- Start date
'...can't rely on action to recreate alphabetized list.'
...what do you mean? you could have the sorted list updated automatically on the addition of data to the main list.
'Also don't want an auto macro slowing down the process'
with only 300 items, i doubt you;d notice
...what do you mean? you could have the sorted list updated automatically on the addition of data to the main list.
'Also don't want an auto macro slowing down the process'
with only 300 items, i doubt you;d notice
Upvote
0
Joe Was
MrExcel MVP
- Joined
- Feb 19, 2002
- Messages
- 7,539
"No VBA, can't rely on action to recreate alphabetized list."
This is not true!
Also the code below is very fast, it will not slow you down!
This is setup to sort coloumn "A" only no other column gets sorted they stay the way they are!
Note: to change this to a different column you must change both the target range and the sort key!
Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code, like: Sheet1.
Sheets("Sheet1").Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
This is not true!
Also the code below is very fast, it will not slow you down!
This is setup to sort coloumn "A" only no other column gets sorted they stay the way they are!
Note: to change this to a different column you must change both the target range and the sort key!
Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code, like: Sheet1.
Sheets("Sheet1").Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Upvote
0
Just assume the cells are random alpha (no numbers) values in cells A1 through A300, with empty cells randomly throughout - want to ignore the empties. Want another column or another sheet to display them in sorted order down a column, with no blanks. If too complex, I'll resort to macros, but wanted to check the board first. Was thinking of possibly the LARGE function but would have to convert to numeric values (possible with CODE) to use that one. Too complicated.
Upvote
0
Similar threads
- Question
