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
<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.
<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?
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?