How to extract pure unique values (No duplicate values)?

Question

I want to separate values which appeared only one time in list.

I am trying to extracting values which do not have any duplicate values. I know how to extract unique values using advance filter, using formulas. But this is new challenge to me.Using a helping column I can do it but trying to do without helping column

And you want this with a normal formula no VBA or VBA function? — Pierre44, Oct 23 '18 at 15:37
Possible duplicate of [Ignore Duplicates and Create New List of Unique Values in Excel](https://stackoverflow.com/questions/13307927/ignore-duplicates-and-create-new-list-of-unique-values-in-excel) — Wizhi, Oct 23 '18 at 15:44
I want only formula. I know how to solve it with vba. But due to some constraints I need only excel builtin functions. — Harun24hr, Oct 23 '18 at 15:47

score 1 · Answer 1 · answered Oct 23 '18 at 15:52

1

If the data is in A2:A23, try this array formula in D2:D23 (or any other column):

=INDEX($A$1:$A$23,SMALL(IF(COUNTIF($A$2:$A$23,$A$2:$A$23)=1,ROW($A$2:$A$23),""),ROW($A$1:$A$22)))

answered Oct 23 '18 at 15:52

jkpieterse

2,727
1
9
18

score 1 · Accepted Answer · answered Nov 22 '18 at 10:10

1

Finally able to solve the problem. Below is formula to extract pure unique values from a list.

=INDEX($B$5:$B$26,AGGREGATE(15,6,(ROW($B$5:$B$26)-ROW($B$4))/(COUNTIF($B$5:$B$26,$B$5:$B$26)=1),ROW(1:1)))

answered Nov 22 '18 at 10:10

Harun24hr

30,391
4
21
36

How to extract pure unique values (No duplicate values)?

2 Answers2