Unique values with Formula NOT Macro

[MDX2013]

I have a column of values. Some are duplicate, some are not. I need a formula to return only unique values without blank cells in the column. See example below.

Raw data	current output	desired output
a	a	a
a		b
b	b	c
c	c
c

<tbody>
</tbody>

Edit:
I should also mention that I am creating a template. Therefore, I do not want to use the "remove duplicates" button or any other solution that involves the user.

 

[mrhstn]

Hi, welcome to the forum.

Couple of options, one is an array which requires ctrl+shift+enter to confirm, the other is normal function which can just be entered

Book1
	A	B	C
1	List	Unique	Unique CSE
2	a	c	a
3	a	b	b
4	a	a	c
5	b
6	b
7	c
8	c
9	c
Sheet2

Cell Formulas
Range	Formula
B2	=IFERROR(LOOKUP(2,1/(COUNTIF($B$1:B1,$A$2:$A$9)=0),$A$2:$A$9), "")
C2	{=IFERROR(INDEX($A$2:$A$9, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$9), 0)), "")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MARZIOTULLIO]

	A​	B​
1​	a	a
2​		b
3​	a	c
4​
5​	b
6​
7​	b
8​
9​	c
10​
11​	c

<tbody>
</tbody>

b1=IFERROR(INDEX($A$1:$A$11,SMALL(IF(FREQUENCY(IF($A$1:$A$11<>"",MATCH($A$1:$A$11,$A$1:$A$11,0)),ROW($A$1:$A$11)-ROW($A$1)+1),ROW($A$1:$A$11)-ROW($A$1)+1),ROWS($A$1:A1))),"")

control+shift+enter copy down

 

[MDX2013]

Worked perfectly!!

Now I just have to find a formula to get the data in a new column with it sorted ascending.

Also, what formula would I use to create a new column with two rows between values? Example:

A A B C B C <tbody> </tbody>

<tbody>
</tbody>

 

[mrhstn]

Perhaps this will get you started

Book1
	A	B
1	List	Unique Sorted
2	John	Andrew
3	Ian
4	Andrew	Bill
5	Bill
6	Bill	Colin
7	Bill
8	Colin	Frank
9	Colin
10	Frank	Ian
11
12		John
Sheet1

Cell Formulas
Range	Formula
B2	{=IFERROR(IF(MOD(ROW(), 2)=0, INDEX($A$2:$A$10, MATCH(SMALL(IF(COUNTIF($B$1:B1, $A$2:$A$10)=0, COUNTIF($A$2:$A$10, "<"&$A$2:$A$10), ""), 1), COUNTIF($A$2:$A$10, "<"&$A$2:$A$10), 0)), ""), "")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MDX2013]

This works fantastic! But one small issue. I don't always know how long my data is going to be. It could go to A10 or A99. But if i change the range to A99 with empty cells, it messes the formula up.

 

[mrhstn]

You would definitely be better with a helper cell/column. But...

Book1
	A	B
1	List	Unique Sorted
2	John	Andrew
3	Ian
4	Andrew	Bill
5	Bill
6	Bill	Ian
7	Bill
8		John
Sheet1

Cell Formulas
Range	Formula
B2	{=IFERROR(IF(MOD(ROW(), 2)=0, INDEX($A$2:INDEX(A:A, MATCH("zzz", A:A)), MATCH(SMALL(IF(COUNTIF($B$1:B1, $A$2:INDEX(A:A, MATCH("zzz", A:A)))=0, COUNTIF($A$2:INDEX(A:A, MATCH("zzz", A:A)), "<"&$A$2:INDEX(A:A, MATCH("zzz", A:A))), ""), 1), COUNTIF($A$2:INDEX(A:A, MATCH("zzz", A:A)), "<"&$A$2:INDEX(A:A, MATCH("zzz", A:A))), 0)), ""), "")}
Press CTRL+SHIFT+ENTER to enter array formulas.