# Copying a list and removing duplicates and blanks

##### Board Regular
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Fluff

##### MrExcel MVP, Moderator
How about in D2 dragged down
Excel Formula:
``=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

##### MrExcel MVP, Moderator
@Fluff
Should that D\$1:D2 be D\$1:D1?
May also need an IFERROR?

Another possibility ..

21 04 02.xlsm
CD
1
2aa
3bb
4cc
5dd
6ar
7ce
8dw
9r
10
11e
12w
13a
14
15
16b
17c
18d
19c
20
Unique List
Cell Formulas
RangeFormula
D2:D20D2=IFERROR(INDEX(\$C\$2:\$C\$40,MATCH(0,INDEX(COUNTIF(\$D\$1:D1,\$C\$2:\$C\$40)+(C\$2:C\$40=""),0),0)),"")

#### Fluff

##### MrExcel MVP, Moderator
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
Excel Formula:
``=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)),"")``

##### Board Regular
You're quite right, I very cleverly copied the formula from D3 & not D2 after adding the error handling on D2
It should be
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
2
Views
538
Replies
3
Views
92
Replies
2
Views
140
Replies
11
Views
189
Replies
23
Views
468

1,136,864
Messages
5,678,216
Members
419,752
Latest member
TryingtoLearnVBA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back