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:

[TABLE="width: 190"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]Abi[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Abi[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Rex[/TD]
[TD]Watermelon[/TD]
[/TR]
[TR]
[TD]Claudia[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Ricky[/TD]
[TD]Grape[/TD]
[/TR]
[TR]
[TD]Abi[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Rex[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Count of Unique Fruit[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td] ARRAY Formula in D2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Abi[/td][td]Apple[/td][td][/td][td]Apple[/td][td] {=IFERROR(INDEX($B$2:$B$8, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$8), 0)),"")}[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Abi[/td][td]Apple[/td][td][/td][td]Watermelon[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Rex[/td][td]Watermelon[/td][td][/td][td]Orange[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Claudia[/td][td]Orange[/td][td][/td][td]Grape[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Ricky[/td][td]Grape[/td][td][/td][td]Pear[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Abi[/td][td]Orange[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Rex[/td][td]Pear[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] ARRAY Formula in D2[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]Abi[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD] {=IFERROR(INDEX($B$2:$B$8, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$8), 0)),"")}[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]Abi[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]Watermelon[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]Rex[/TD]
[TD]Watermelon[/TD]
[TD][/TD]
[TD]Orange[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]Claudia[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD]Grape[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD]Ricky[/TD]
[TD]Grape[/TD]
[TD][/TD]
[TD]Pear[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD]Abi[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

[/TD]
[TD]Rex[/TD]
[TD]Pear[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="width: 724"]
<tbody>[TR]
[TD]
=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),"")
[/TD]
[/TR]
</tbody>[/TABLE]


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td]Duplicated items[/td][td]NORMAL formula in D2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Abi[/td][td]Apple[/td][td][/td][td]Pear[/td][td] =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),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Abi[/td][td]Apple[/td][td][/td][td]Orange[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Rex[/td][td]Watermelon[/td][td][/td][td]Apple[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Claudia[/td][td]Orange[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Ricky[/td][td]Grape[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Abi[/td][td]Orange[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Rex[/td][td]Pear[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Rhona[/td][td]Pear[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]