Return Unique Value in Excel Using Formula

[andersen_yunan]

Hi, is there any ways to return unique value in excel using formula? For instance as below:

Name	Fruit
Abi	Apple
Abi	Apple
Rex	Watermelon
Claudia	Orange
Ricky	Grape
Abi	Orange
Rex	Pear
Count of Unique Fruit	5

<tbody>
</tbody>

Unique Value
Apple
Watermelon
Orange
Grape
Pear


Thanks in advance!

 

[sparky2205]

https://exceljet.net/formula/extract-unique-items-from-a-list

 

[Yongle]

Try this:

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​					ARRAY Formula in D2
2​	Abi	Apple		Apple	{=IFERROR(INDEX($B$2:$B$8, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$8), 0)),"")}
3​	Abi	Apple		Watermelon
4​	Rex	Watermelon		Orange
5​	Claudia	Orange		Grape
6​	Ricky	Grape		Pear
7​	Abi	Orange
8​	Rex	Pear
9​

Sheet: Sheet1

ARRAY formula in D2 copied down
=IFERROR(INDEX($B$2:$B$8, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$8), 0)),"")

An array formula must be committed with CTRL}{SHIFT}{ENTER} and then it will be enclosed in curly brackets and look like this (you cannot type in the brackets!!)
{=IFERROR(INDEX($B$2:$B$8, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$8), 0)),"")}

 

[andersen_yunan]

Try this:

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​					ARRAY Formula in D2
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Abi	Apple		Apple	{=IFERROR(INDEX($B$2:$B$8, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$8), 0)),"")}
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Abi	Apple		Watermelon
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Rex	Watermelon		Orange
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	Claudia	Orange		Grape
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	Ricky	Grape		Pear
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	Abi	Orange
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	Rex	Pear
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

ARRAY formula in D2 copied down
=IFERROR(INDEX($B$2:$B$8, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$8), 0)),"")

An array formula must be committed with CTRL}{SHIFT}{ENTER} and then it will be enclosed in curly brackets and look like this (you cannot type in the brackets!!)
{=IFERROR(INDEX($B$2:$B$8, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$8), 0)),"")}

Hi Yongle,

Thanks for your response! How about the other way around when i want to return the duplicate value only?
For instance:
Apple
Orange

 

[Yongle]

NORMAL formula in D2 copied down

=IFERROR(LOOKUP(2,1/(((COUNTIF($D$1:D1, $B$2:$B$9)=0)*(COUNTIF($B$2:$B$9, $B$2:$B$9)>1))), $B$2:$B$9),"")

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​				Duplicated items	NORMAL formula in D2
2​	Abi	Apple		Pear	=IFERROR(LOOKUP(2,1/(((COUNTIF($D$1:D1, $B$2:$B$9)=0)*(COUNTIF($B$2:$B$9, $B$2:$B$9)>1))), $B$2:$B$9),"")
3​	Abi	Apple		Orange
4​	Rex	Watermelon		Apple
5​	Claudia	Orange
6​	Ricky	Grape
7​	Abi	Orange
8​	Rex	Pear
9​	Rhona	Pear
10​

Sheet: Sheet1