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
– Pierre44, Commented Oct 23, 2018 at 15:37
Possible duplicate of Ignore Duplicates and Create New List of Unique Values in Excel — Wizhi
– Wizhi, Commented Oct 23, 2018 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
– Harun24hr, Commented Oct 23, 2018 at 15:47

jkpieterse · Accepted Answer · 2018-10-23 15:52:01Z

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, 2018 at 15:52

jkpieterse

3,0591 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harun24hr · Accepted Answer · 2018-11-22 10:10:02Z

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, 2018 at 10:10

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related