How to copy the filter list in excel

[ravikumarnv60]

I have an excel with list of names. I want to copy the unique names. To get unique names, I am using filters. Now how can copy the names showing up in filters drop down list?

[#2]

@ravikumarnv60 

Let's say that your list of names is in A2:A100, and that you want to extract the unique names into cell D2 and down.

Enter the following formula in D2 and confirm it with Ctrl+Shift+Enter to save it as an array formula:

=IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF(D$1:D1,$A$2:$A$100),0)),"")

Fill down as far as you want.

[#3]

Is there a way to just copy the list showing up in the filter drop down list?

[#4]

@ravikumarnv60 

No, there isn't

[#5]

I have used your formula but i am getting the list with repeating values. I need all values without any duplicates

I am getting like below
Plant
Plant
Plant
Plant
Site
...
I need like below
Plant
Site
...

[#6]

@ravikumarnv60 

Strange - it should work.

Do you have Microsoft 365? If so, you can use the new UNIQUE function - see Excel UNIQUE Function 

[#7]

I was facing the same issue and this video helped me get my desired result:

Excel Magic Trick #244: Advanced Filter Extract To New Sheet (Word Criteria)

https://youtu.be/_KGqJLXJvgY

At first, it didn't work. I had to clear my filter for the Advanced Filter use all the data in the array.

Cheers!

[#8]

@ravikumarnv60 

What I did was use sort the column list to be the names that I wanted.

Then I highlighted the column with the names and clicked on remove duplicates on the Data tab.

This left just the names  that I wanted. So I just highlighted the column and copied them. After which you can just undo the previous changes. Bit of a workaround but whatever it worked. 

[#9]

What i did to tackle this challenge was:

- copy the column itself ,

- then pasted in a new excel spreadsheet

 - In the new excel spreadsheet, select this column

- go to Data tab, then select remove duplicates.

after that you can the copy the unique names.

seestep 4step 3step 2Step 1 snapshots of the steps.

hope someone else finds this helpful.

@ravikumarnv60 

[#10]

@Mark_Azzy  Thanks for the tutorial link.

[#11]

This proved to be helpful to me! Thank you, @ravikumarnv60