Formula for finding unique values

[Ben Clinton]

Do you know what formula to use in tis example if I want to find a unique value in column B?29009

[anish.ms]

=UNIQUE(B2:B11)
Currently the UNIQUE function is only available with Office 365 subscriptions. Excel 2019, Excel 2016, Excel 2013 and earlier do not support dynamic array formulas, so the UNIQUE function is not available in these versions.

[mancubus]

https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

[Ben Clinton]

Thanks for your advice:)

[Bob Phillips]

If you don't have Office 365 and the UNIQUE function, try this.

In cell D2, enter -
=B2

In cell D3, add this array formula -
=IFERROR(IF(INDEX($B$2:$B$100,MATCH(0,COUNTIF(D$2:D2,$B$2:$B$100&""),0))=0,"",INDEX($B$2:$B$100,MATCH(0,COUNTIF(D$2:D2,$B$2:$B$100&""),0))),"")

copy D3 down as far as you need.

[Paul_Hossler]

FYI, the :D is 'translated' by the forum software. It really is ":D". i.e. part of the range specification

Took me awhile long ago to figure out why Ranges were laughing at me :yes

[anish.ms]

Thanks Bob for this formula:bow: and thanks Paul for clarifying the range converted into smiley :clap:
Just a doubt - Is there any specific reason for having &"" in the count if formula
COUNTIF(D$2:$D2,$B$2:$B$100&"")

[Bob Phillips]

Sorry about the smilies, I should look at my posts after I post them :-)


Just a doubt - Is there any specific reason for having &"" in the count if formula
COUNTIF(D$2:$D2,$B$2:$B$100&"")

It is there so as to handle the case where there are blank cells in the data.