Table column as range name

[mark hansen]

I'm trying to pull a unique list of names, in alphabetical order from a column in a table.

I picked a very helpful formula from Andrew Poulsom regarding getting a unique list of name in alphabetical order and it works great... The thread is"

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

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.

www.mrexcel.com

I modified the formula to:
{=IFERROR(INDEX(UniqueShortNurseList,MATCH(0,COUNTIF(UniqueShortNurseList,"<"&UniqueShortNurseList)-SUM(COUNTIF(UniqueShortNurseList,"="&Z$35:Z35)),0)),"")}
And it works fine

UniqueShortNurseList is a range pulled from my table using this formula:
{=IF(IFERROR(INDEX(Table1[User Name sending data],MATCH(0,COUNTIF(AA$34:$AA34,Table1[User Name sending data]),0)),"")=0,"",IFERROR(INDEX(Table1[User Name sending data],MATCH(0,COUNTIF(AA$34:$AA34,Table1[User Name sending data]),0)),""))}

The second formula doesn't put the list in alphabetical order. (That's what I did first, being lazy, but the client wanted them in alpha order... Don't blame them, the list will get long)

So the first formula uses the list create by the second formula sort of as a helper list... But I'm thinking, I don't need to do this... I thinking (hoping) I can do this:
{=IFERROR(INDEX(Table1[User Name sending data],MATCH(0,COUNTIF(Table1[User Name sending data],"<"&Table1[User Name sending data])-SUM(COUNTIF(Table1[User Name sending data],"="&Z$35:Z35)),0)),"")}

But using the "Table1[User Name sending data]" as the range name doesn't seem to work.

All are entered with CTRL+SHIFT+ENTER

Am I barking up the wrong tree?
Thanks for any insight.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mark hansen]

Thanks, we are using Office 2016 but may be going to 365 in a few months.

 

[Fluff]

Thanks for that, your final formula should work. Is it in Z36 dragged down?
Also do you have any blank cells in the table?

 

[mark hansen]

The formula is pasted into Z36 and entered with CTRL+SHIFT+Enter and dragged down. I did have a blank cell in that column and when I filled that in, it worked... I didn't even look for blank cells.

I appreciate the help.

I didn't realize a blank cell would affect the formulas with the table address. In doing some testing, I would erase a cell in that column, and the last formula with the Table address didn't like the blank cell... The other two formulas were not affected.

Thanks again for your help and insight
Mark

 

[Fluff]

You're welcome & thanks for the feedback.