Excel – Listing all unique values from table in another sheet

microsoft excelmicrosoft-excel-2013

In Excel, I have a table where I want to list all unique values from a column in a different a row of a different sheet. Here is an example of the data I have:

Example of Table

I want to fill in cells in the other sheet so that it gives all of the different options for the favorite fruit. It would look something like this:

Apple | Banana | Grapefruit | Orange

How do I loop through the options (shown below) to make this happen?
List Options

Best Answer

Check this article that explains to get unique values from a list. Differently, it creates a vertical list however, formula will work as horizontal as well. However; you should add | characters by yourself, I mean by using another formula.

Unique list formula is

=LOOKUP(2,1/(COUNTIF($D$2:D2,$B$3:$B$9)=0),$B$3:$B$9)

which

  • $D$2:D2 is one cell at left (or above if you want vertical list)
  • $B$3:$B$9 is the range that contains all values (your actual data)

After tying formula, all you need to do is copy the formula to right cells.

Related Question