List unique values alphabetically

[eduzs]

Hello,
I have this formula that works fine to list in alphabetical order:

=IFERROR(INDEX(B$3:B$11,AGGREGATE(15,6,(ROW(B$3:B$11)-ROW(B$3)+1)/(B$3:B$11<>""),ROWS(D$3:D3))),"")

Could anyone help me to adapt this formula so that it also returns only unique values?
I need it not to be a array formula.

Thanks

 

[XOR LX]

Hi,

That formula itself does nothing to "list in alphabetical order", but simply returns non-blanks. So unless the original entries are already in alphabetical order, I'm not sure what you mean.

Can you clarify?

Regards

 

[eduzs]

Sorry, the formula above actually return non blanks.
I need a non array formula that list unique values alphabetically.
Thanks

 

[eduzs]

This is the array formula that do what I need (list unique values in alphabetical order):

{=IFERROR(INDEX($C$2:$C$151,MATCH(0,COUNTIF($C$2:$C$151,"<"&$C$2:$C$151)-SUM(COUNTIF($C$2:$C$151,"="&E$1:E1)),0)),"-")}

The problem is that I want to share this workbook and excel returns an error that says array formulas are not accepted in the shared workbook.

Thanks

 

[XOR LX]

That formula will not work on its own, but requires an initial entry to be inserted into cell E1, I believe, which perhaps you should have shared with us.

Anyway, a non-CSE version of that formula is:

=IFERROR(INDEX(C$2:C$151,MATCH(0,INDEX(COUNTIF(C$2:C$151,"<"&C$2:C$151)-SUMPRODUCT(COUNTIF(C$2:C$151,E$1:E1)),),0)),"-")

Regards

 

[eduzs]

Superb! Works like a charm. Thanks a lot.

 

[eduzs]

Just one more question, how can I modify the formula to ignore blank cells in range C$2:C$151?
Thanks

 

[XOR LX]

Just one more question, how can I modify the formula to ignore blank cells in range C$2:C$151?

That formula does currently ignore blank cells, if by blank you mean empty. If those 'blanks' actually contain the null string (""), e.g. as a result of formulas in those cells, you'll need:

=IFERROR(INDEX(C$2:C$151,MATCH(0,INDEX(COUNTIFS(C$2:C$151,"<>",C$2:C$151,"<"&C$2:C$151)-COUNTIF(C$2:C$151,"")-SUMPRODUCT(COUNTIF(C$2:C$151,E$1:E1)),),0)),"-")

If the range potentially contains both 'genuine' blanks and null strings then you'll require a different set-up.

Of course, you may well need to change your formula in E1 as well, though not having seen that one I couldn't say for sure.

Regards