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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Raw data[/TD]
[TD]current output[/TD]
[TD]desired output[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]a[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.