Exclude Headers from Table/Named Range in Formula Outcomes

[Mydako]

Hello all,

I'm trying to make a column that automatically grabs the values from another column, makes sure they are unique, and then alphabetizes them.

Animal[TABLE="width: 500"]
<tbody>[TR]
[TD]Alpaca[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]Elephant[/TD]
[/TR]
</tbody>[/TABLE]


Should return


Alpaca
Cat
Dog
Elephant

This is the formula I'm using

{=IFERROR(INDEX(Name,MATCH(-1,COUNTIF(Name,"<"&Name)-SUM(COUNTIF(Name,"="&A$1:A1)),0)),"")}

Name is the Named Range. I'm used a named range in a table generated by Excel (Format as Table option) because this list will grow and this will automatically update, rather than using a huge column reference (A1:A7000).

This formula is entered in A2

The formula works almost perfectly...but for the life I me I can't get it to stop grabbing the header from the named range.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Animal
[/TD]
[/TR]
[TR]
[TD]Alpaca[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]Elephant[/TD]
[/TR]
</tbody>[/TABLE]

Animal is the Header, it's understood by the table/range as the header.

But if "Animal" is the Header my result is

Alpaca
Animal
Cat
Dog
Elephant


It keeps grabbing and alphabetizing the header, how can I get it to stop doing this? I've tried switching around the -1,0,1 values but that doesn't seem to fix it.

Any insight?

 

[Mydako]

Update: I've been toying around with no luck, but I came across an oddity that might explain things to those who know better.

I don't know if this makes a difference, but if the header in the named range (Animal) is different than the header of A Column where I put the formula, then is doesn't work. If one says Animal and one says Animals it won't return anything, but if they match, then it will populate everything. It doesn't matter what they're titled, they just have to match.

Expanding:

If the Animal column is D, D1 says "Animal". Then with the formula being put in A2, if A1 does not say "Animal" then nothing will be returned in A2 or below.

 

[Marcelo Branco]

Try this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Animal​

[/td][td][/td][td][/td][td]

Animal​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Alpaca​

[/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Alpaca​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Cat​

[/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Dog​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Dog​

[/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Cat​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Elephant​

[/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Dog​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Elephant​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

The named range Names is set as A2:A6 (gray area), not A1:A6

Array formula in A2 copied down
=IFERROR(INDEX(Names,MATCH(0,COUNTIF(Names,"<"&Names)-SUM(COUNTIF(A$1:A1,Names)),0)),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Mydako]

Thanks. It seems obvious now, but I was letting Excel define the ranges automatically in a table, and when I'd highlight down even if I started from A2 it would include A1 as it's part of the table. Manually set the name range, and used your formula Marcelo, and now it works perfectly. Thanks muchly.