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

Question

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.

Answer 1

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.

Answer 2

You can use the following formula:

=UNIQUE({FILTER(A1:A7;A1:A7<>"");FILTER(B1:B7;B1:B7<>"")})

Let's explain it:

• FILTER(A1:A7;A1:A7<>"") --> column A is filtered so that we retrieve only the values that are not blank
• FILTER(B1:B7;B1:B7<>"") --> the same happens for column B
• We put both functions inside brackets, {}, so we transform the data into a vertical array
• UNIQUE --> this function allows us to drop repeated values