mark hansen
Well-known Member
- Joined
- Mar 6, 2006
- Messages
- 534
- Office Version
- 2016
- Platform
- Windows
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"
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.
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.