Alphabetize a unique list based on criteria

Hey everyone, I'm successfully using this index to get unique values from a list. But how can I alphabetize this list?

Please Login or Register  to view this content.

Attached one possible way with a helper column.

=COUNTIF(B$2:B$21,"<"&B2)+1 to just rank the data and then a simple index/match to sort.

Regards

Hi Raul,
can you please explain how does the countif work here?

thanks

btw - unichar() might not be available in Excel 2010 (only since Excel 2013 I think) if you're still using that; but the char() function provides for the same data for all normal purposes (values 1 to 255).

Regards

Thank you all so much for your help. You put me on the right track.

@bebo: As per the first post unique values were already extracted.

Regards

Just a note about the $B$1 cell, this is a drop down that when I select a different value I get a unique list based on what I have selected from the drop list

Please Login or Register  to view this content.

I want thank you guys for the help you have provided. I'm applying this solution.

Please Login or Register  to view this content.

My next goal is to get the below formula to ignore blank cells. But this works as long as the cells have data in them.

RaulSerg ,

Thank you for the deatailed explanation. this is really interesting!
BTW - when I try to apply this formula =A>a I get #NAME?
Can you please explain why?

I indeed dont have the unichar function available, and also the CHAR function, although available, returns a symbol instead of a number. :/

="A">"a"

char() is opposite of code()

You know, that long formula I posted earlier looked a bit awkward. So here is a shorted version using a self expanding named range. Here is the challenge that I'm having. I can't see to be able to add a criteria to the formula that will let me select the list I want to see based on a dropdown.

Any thoughts anyone?

Please Login or Register  to view this content.

The criteria that I want to use in the formula would access column B. Say for example, If I select FOOD, then only those items categorized as FOOD will populate the list. I've attached the workbook for everyone to look at.

Try to just add the condition? That is: instead of: COUNTIF($A$2:A2,MyCRange)=0
Do: (COUNTIF($A$2:A2,MyCRange)=0)*(OFFSET(MyCRange,,-1)=$B$1)
This works for me.

btw - you should remove the spaces from your formulas; in Excel spaces are the intersection operation which may or may not cause unexpected problems with your formulas

Regards

Thank you for your thoughts. But I'm getting an error that won't allow me to apply the change. The error occurs at the =0 portion of the formula. What am I doing wrong?

Sounds like you got the brackets wrong.

=IFERROR(INDEX(MyCRange,MATCH(SMALL(IF((COUNTIF($A$2:A2,MyCRange)=0)*(OFFSET(MyCRange,,-1)=$B$1),COUNTIF(MyCRange,"<"&MyCRange),""),1),COUNTIF(MyCRange,"<"&MyCRange),0)),"")

Maybe this will help. I've added another named range called MyBRange. I've also added an image that shows the columns used by the array. Column B contains the category criteria (MyBRange)

Okay, I'm able to apply the formula below but I'm not getting any results, just a blank space. The MyBrange is the category column. The MyCRange is the Items columns. B$7 should look in column B (Categories) and based on the category selected it will only display those items (MyCRange) that have the category.

Please Login or Register  to view this content.

I am not sure what more I can add. The formula I provided is copy paste and works fine for me.
Adding more named ranges or whatever is unnecessary.

Here's your workbook. Let me know if that works for you or what exactly is not giving you the result you expect.

Okay, you are a genius. I'm not sure what I was missing but you solved it RaulSerg!! Thank you so much.