Getting Unique Values from List without VBA

[Hazelwood]

Hi, I'm using Excel (Microsoft 365 on a Mac)

I'm using the formula

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

B1 = Fruit, B2 etc = Apple, Orange, Apple, Banana, Banana, Apple, Pineapple, Peach
D1 = Unique, D2 has the above formula

I copied the example from https://www.extendoffice.com/ into my spreadsheet (will convert to my application once it works)

When I use the formula builder, it shows the result as expected (D2 being Apple, but in my spreadsheet it only shows a blank - I'm stumped as to how to get it to show the first unique value and subsequent values.

Any ideas would be be much appreciated... Hazelwood

 

[Tom Urtis]

To list the unique items in the order they appear in the list, enter this array formula in cell D2 and copy down as needed:

=INDEX($B$2:$B$9,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$9),0))

To list the unique items in alphabetical order, enter this array formula in cell D2 and copy down as needed:

=INDEX($B$2:$B$9,MATCH(0,COUNTIF($B$2:$B$9,"<"&$B$2:$B$9)-SUM(COUNTIF($B$2:$B$9,"="&D$1:D1)),0))

Note that these are ARRAY formulas. You confirm them to the cell by pressing Ctrl+Shift+Enter, not just with Enter.

 

[Hazelwood]

To list the unique items in the order they appear in the list, enter this array formula in cell D2 and copy down as needed:

=INDEX($B$2:$B$9,MATCH(0,COUNTIF($D$1:D1,$B$2:$B$9),0))

To list the unique items in alphabetical order, enter this array formula in cell D2 and copy down as needed:

=INDEX($B$2:$B$9,MATCH(0,COUNTIF($B$2:$B$9,"<"&$B$2:$B$9)-SUM(COUNTIF($B$2:$B$9,"="&D$1:D1)),0))

Note that these are ARRAY formulas. You confirm them to the cell by pressing Ctrl+Shift+Enter, not just with Enter.

Tom, much appreciated, have a great day ... Hazelwood