Formula to return unique values in a range in alphabetical order?

[mrmiyagi]

Hello,

I have a column A in a sheet called 'Project Management'.

What I want to do is use a formula to return a list of distinct project names from column a in alphabetical order.

Is this possible?

Thank you.

 

[ridolfo]

Hi Andrew -

I have been looking for a formula like this and this works great. I have one question for you - I cant seem to get this to work when my "a2:a11" range is numbers formatted as text, IE '012 '015 '001. This will give me the first value but the rest seem to crash and I get an #N/A error. Any thoughts on how to get around this?

Thanks!
Bob

 

[Andrew Poulsom]

Can't you enter numbers formatted as 000?

 

[bsquad]

Andrew, this was a great formula and had worked for me (I had tried 2 other long formulas with no luck). Is there a way to order based on the count of the distinct values? - and/or use LARGE/SMALL function within this formula?

Thank you!

 

[Richard U]

If your data is in A2:A11, in B2 type:

=INDEX($A$2:$A$11,MATCH(0,COUNTIF($A$2:$A$11,"<"&$A$2:$A$11)-SUM(COUNTIF($A$2:$A$11,"="&B$1:B1)),0))

and press Ctrl+Shift+Enter not just Enter. If correctly entered Excel will surround this array formula with curly braces {}. Copy the formula down as far as necessary.

Hi Andrew, could you post a bit of an explanation as to how it works? I'm strong on VBA but weak on formulas.

 

[Andrew Poulsom]

The Evaluate Formula tool will show you how the formula works.

 

[mark hansen]

Thanks for the formula Andrew, Just what I needed!!