Table column as range name

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
522
Office Version
  1. 2016
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 6, 2006
Messages
522
Office Version
  1. 2016
Thanks, we are using Office 2016 but may be going to 365 in a few months.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, your final formula should work. Is it in Z36 dragged down?
Also do you have any blank cells in the table?
 
Solution

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
522
Office Version
  1. 2016
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,700
Messages
5,637,882
Members
416,988
Latest member
Ahmed_Yehia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top