Q:

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.

A:

you can use this formula:
=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)))

It iterates through the first column of values till it errors, then iterates the second as it is dragged down. 
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.

