Ongoing alphabetized list

Sneech#7

Active Member
Joined
Oct 22, 2004
Messages
284
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
'...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
 
Upvote 0
"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
 
Upvote 0
Guys, with all due respect, I know several VBA options... I'm wondering if there is a non-VBA solution that makes sense, if anyone has done this type of thing with cell functions. :-D
 
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

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top