Copying a list and removing duplicates and blanks

[glad_ir]

Hello,

Please could I get some help.

I have a list in my sheet in cells C2:C40 consisting of data (selected from a drop down menu) and blank cells. I'd like to copy just the unique entries from this list into cells D2:D40, removing duplicate entries and blanks. Is this possible with a formula?

Any help would be much appreciated

Thank you,
Iain

 

[Fluff]

How about in D2 dragged down

=INDEX($C$2:$C$40,AGGREGATE(15,6,(ROW($C$2:$C$40)-ROW($C$2)+1)/($C$2:$C$40<>"")/(ISNA(MATCH($C$2:$C$40,D$1:D2,0))),1))

 

[Peter_SSs]

@Fluff
Should that D$1:D2 be D$1:D1?
May also need an IFERROR?

Another possibility ..

21 04 02.xlsm
	C	D
1
2	a	a
3	b	b
4	c	c
5	d	d
6	a	r
7	c	e
8	d	w
9	r
10
11	e
12	w
13	a
14
15
16	b
17	c
18	d
19	c
20
Unique List

Cell Formulas
Range	Formula
D2:D20	D2	=IFERROR(INDEX($C$2:$C$40,MATCH(0,INDEX(COUNTIF($D$1:D1,$C$2:$C$40)+(C$2:C$40=""),0),0)),"")

 

[Fluff]

Should that D$1:D2 be D$1:D1?
May also need an IFERROR?

You're quite right, I very cleverly copied the formula from D3 & not D2 after adding the error handling on D2
It should be

=IFERROR(INDEX($C$2:$C$40,AGGREGATE(15,6,(ROW($C$2:$C$40)-ROW($C$2)+1)/($C$2:$C$40<>"")/(ISNA(MATCH($C$2:$C$40,D$1:D1,0))),1)),"")

 

[glad_ir]

You're quite right, I very cleverly copied the formula from D3 & not D2 after adding the error handling on D2
It should be

=IFERROR(INDEX($C$2:$C$40,AGGREGATE(15,6,(ROW($C$2:$C$40)-ROW($C$2)+1)/($C$2:$C$40<>"")/(ISNA(MATCH($C$2:$C$40,D$1:D1,0))),1)),"")

This works a treat. Thank you both very much.

cheers, Iain

 

[Fluff]

Glad we could help & thanks for the feedback.