Exclude Headers from Table/Named Range in Formula Outcomes

Mydako

New Member
Joined
Apr 19, 2018
Messages
26
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
Alpaca
Dog
Cat
Dog
Elephant

<tbody>
</tbody>


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.

Animal
Alpaca
Dog
Cat
Dog
Elephant

<tbody>
</tbody>

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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Mydako

New Member
Joined
Apr 19, 2018
Messages
26
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,397
Try this


A
B
C
D
1
Animal​
Animal​
2
Alpaca​
Alpaca​
3
Cat​
Dog​
4
Dog​
Cat​
5
Elephant​
Dog​
6
Elephant​
7

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

New Member
Joined
Apr 19, 2018
Messages
26
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,473
Messages
5,548,246
Members
410,824
Latest member
Bobmn4
Top