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.

 

[Randombard]

I cant think of a way to do it with a formula,

would you be happy with VBA if I talked you through it?

 

[mrmiyagi]

I cant think of a way to do it with a formula,

would you be happy with VBA if I talked you through it?

Thank you for your reply. Sure I would be happy to use VBA. Thank you.

 

[Andrew Poulsom]

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.

 

[Richard Schollar]

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.

That's an awesome formula Andrew

 

[mrmiyagi]

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.

Thank you.

 

[mrmiyagi]

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.

Thank you for your reply.

I am going to use the formula to list distinct values in column A as a source for data validation in column B?

Will this formula work for this purpose?

 

[Andrew Poulsom]

That's an awesome formula Andrew

Not mine. Just adapted from Biff's here:

http://groups.google.com/group/micr...2fc29?lnk=st&q=&rnum=1&hl=en#796533297442fc29

 

[Andrew Poulsom]

Thank you for your reply.

I am going to use the formula to list distinct values in column A as a source for data validation in column B?

Will this formula work for this purpose?

You will need to use the entries in column B as the source for your data validation. You can use a dynamic range:

http://www.contextures.com/xlDataVal08.html#Dynamic

 

[Mike Szczesny]

Hi Andrew,

Great formula to sort the results. I am trying to figure out this formula works. Can you shed some light on how this formula works. What do the countif functions inside the formula seek to do?
Thanks,
Mike Szczesny