Pull Unique Values from a List and Automatically Sort in Alphabetical Order

**Aimee S.** · 08-28-2013, 11:55 AM

Hi Folks.

Here's an example column for the question I have:

SAP Project Manager
Smith, Randall
Collins, Todd
Padgett, Leslie
Schwankl, Thomas
Schwankl, Thomas
Olson, Jeffrey
Padgett, Leslie
Richards, Chad
Korth, Kurt
Plesnyayev, Vitaliy
Smith, Christopher
Olson, Jeffrey
Schwankl, Thomas
Smith, Christopher
Smith, Christopher
Richards, Chad
Collins, Todd
Collins, Todd
Collins, Todd
Collins, Todd
Olson, Jeffrey
Collins, Todd
Padgett, Leslie
Steele, Terri
Grimsley, Stephen
Collins, Todd
Padgett, Leslie
Padgett, Leslie

My question is, is there a way to make a table that always, upon saving, pulls the unique values from that list and sorts them from A to Z. Fresh data would be pasted into this source column every week, but the column would remain identical in its location, so the formula that accomplishes what I need won't break. Any help is appreciated. The simpler the solution the better. Cheerio and thanks for your time.

**alansidman** · 08-28-2013, 12:01 PM

Would it be ok if the solution was to delete all duplicates and then sort alpha? If that will work for you, then insert this in a module.

Please Login or Register  to view this content.

Alan

**alansidman** · 08-29-2013, 09:20 AM

If you are not inclined to a VBA solution, this video just made it to my inbox. Thought you might be interested in it.

http://www.youtube.com/watch?v=7Rjh-...em-subs_digest

**JBeaucaire** · 08-29-2013, 09:43 AM

Admittedly I have not watched the video, Alan.

Assuming the data is in A1 going downward, these two formulas will do the job:

1) In B1, enter this array formula to get the first alphabetized value:

=INDEX(A$1:A$100,MATCH(0,COUNTIF(A$1:A$100,"<"&A$1:A$100),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

2) To get the rest of the values, enter this array in B2, then copy B2 down a ways:

=IF(COUNTIF(A$1:A$100,">"&B1), INDEX(A$1:A$100, MATCH(COUNTIF(A$1:A$100,"<="&B1), COUNTIF(A$1:A$100,"<"&A$1:A$100),0)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array.