# Excel: get list of unique values from a table and place the values in one column using formulas

I have a table of values that span across a range of rows and columns. Example data:

``````Green    Leaf
Green    Fire
Red      Fire
Water    Blue
Blue
Red
Water
``````

I would like a single column of unique values from the table. Result:

``````Green
Leaf
Fire
Red
Water
Blue
``````

I would prefer to use only formulas if possible. I have tried using the Advanced Filter Tool in the Data ribbon menu shown here, but it results in two columns instead of one.

• Must it be formulas? Could it be VBA?. If it has to be formulas, can you use helper columns? Also: can a given value appear in both columns – cybernetic.nomad Oct 10 '18 at 18:33
• You can't break the data down to one column? If you can do that then all you need to do is attach an assist column that puts an index for those that are unique and then collect the values with an index in the final column – Eric F Oct 10 '18 at 18:36
• @cybernetic.nomad I can use helper columns. A given value can appear in both columns, but not on the same row. – EarthIsHome Oct 10 '18 at 18:37
• @EricF No can do; this data is part of a larger table with more data on the same row. (i.e. green and leaf correspond to more data on the same row.) – EarthIsHome Oct 10 '18 at 18:39
• @EricF Is there a way of breaking the columns down into one using formulas? – EarthIsHome Oct 10 '18 at 18:43

``````=IFERROR(INDEX(\$A\$1:\$A\$7,AGGREGATE(15,7,ROW(\$A\$1:\$A\$7)/(COUNTIF(\$D\$1:D1,\$A\$1:\$A\$7)=0),1)),INDEX(\$B\$1:\$B\$4,AGGREGATE(15,7,ROW(\$B\$1:\$B\$4)/(COUNTIF(\$D\$1:D1,\$B\$1:\$B\$4)=0),1)))
The important thing to remember is that it needs to be in at least the second row and the `\$D\$1:D1` should refer to the cell directly above the cell in which the formula is first placed, paying attention to what is and what is not absolute in the reference.