how to generate alphabetical validation list

[jontherev]

I am currently using the following formula to generate a column without blanks (copying it down of course), and then using that column to generate a list to use in a validation list. The problem, is that my list is not necessarily in alphabetical order and can't be changed on the original list. What is the best way to get an alphabetical list in order to use in a validation list? I should only have unique values, if that matters.

Please Login or Register  to view this content.

Thanks in advance!!

[Tony Valko]

If using formulas, you would then create another list that contains the unique sorted items.

There's an alternative method where the list does not need to be sorted. You can type in a few letters (or a word) and have the drop down automatically present selections based on what you typed:

http://www.youtube.com/watch?v=vkPoViUhkxU

[jontherev]

Thanks, but I don't want the user to type...just use the drop down. For a long list of options, the user may not remember what to even type. So, what is the easiest way to sort unique items so that it alphabetizes them? I would then use THAT list to generate my validation list drop box. In case it matters, I may have more than one word in a particular cell. If there's a macro that does this, that is fine as well. Thanks for your time, much appreciated.

[Tony Valko]

Can you show me the list that's generated by your formula in post #1?

BTW, if you're using Excel 2007 or later you can reduce that to:

=IFERROR(INDEX($O$376:$O$401,SMALL(IF($O$376:$O$401<>"",ROW($O$376:$O$401)),ROWS(S$376:S376))-ROW($O$376)+1),"")

Still array entered.

[jontherev]

Of course! And thanks for the idea. I might have users with old versions or using Open Office...does iferror translate ok to Open/Libre Office?

The completely random list:
Abraham Lincoln
George Washington
George Bush
Hillary Clinton
Stephen Colbert
Paula Abdul

This is out of order, and I want to make it alphabetical. Thanks!

[Tony Valko]

Not sure about those other Office applications.

We'll try another error trap that works in all versions of Excel.

Data Range

	A	B	C
1	List1	------	Sorted List1
2	Abraham Lincoln		Abraham Lincoln
3	George Washington		George Bush
4	George Bush		George Washington
5	Hillary Clinton		Hillary Clinton
6	Stephen Colbert		Paula Abdul
7	Paula Abdul		Stephen Colbert
8	George Washington
9
10

I'm assuming there will be cells that contain formula blanks in List1. The formula to extract and sort those entries will not work if there are formula blanks so we have to create a dynamic range to eliminate those cells that do contain formula blanks.

Create the dynamic range...

Goto the Formulas tab>Define name
Name: List1 (or whatever you want to call it)
Refers to: =OFFSET($A$2,,,COUNTIF($A$2:$A$10,"?*"))

Adjust for a reasonable end of range where I use down to A10.

Ok, let's create the sorted list...

There must be a cell above the first formula cell and this cell must not contain an entry that also appears in List1. Typically, this cell would be a column header.

Enter this array formula** in C2:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(List1,MATCH(0,COUNTIF(List1,"<"&List1)-SUM(COUNTIF(List1,C$1:C1)),0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Then, as the source for the drop down list use:

=OFFSET($C$2,,,COUNTIF($C$2:$C$10,"?*"))

Adjust for a reasonable end of range where I use down to C10.

[jontherev]

That works perfectly. Thanks so much!

[Tony Valko]

You're welcome. Thanks for the feedback!