Make data list and copy from a list

**codepin** · 03-19-2009, 03:21 AM

Hi all,

I have a list of names in column A, don't know how many different names but for sure some of them repeats in different cells of column A.

A1 = NAME_1
A2 = Alan
A3 = Ben
A4 = John
A5 = Alan
A6 = Kevin
A7 = Mark
A8 = Dominic
A9 = John
A10= Tom
A11=Alan
A12=Frank
.
.
.

How can I make aonther list in column B, which contains a list of name in column A without repeating. So that B1 = NAME_2, B2:B4 is exactly like A2:A4, then B5 = Kevin, B6 = Mark, B7 = Dominic, B8 = Tom, B9 = Frank.... and so on.

Another question, which is very similar to the previous one. In the same column A, now I add an AutoFilter (Data > Filter > AutoFilter) in A1. A drop down arrow button will appear at the right side within A1. Click the down arrow and another box shows: Sort Ascending, Sort Descending, All, Top 10, Custom, Alan, Ben.... the rest of the data in Column A but each unique data will only shows once in that list. How can I copy the contents of that box?

My thanks in advance.

**zbor** · 03-19-2009, 03:40 AM

When you autofilter it copy just as anything else (select -> Ctrl+C) and paste (Ctrl+V)

**JBeaucaire** · 03-19-2009, 03:42 AM

Not sure where this macro came from, but it's in my .bas catalog:

Please Login or Register  to view this content.

Put that macro into a standard module.

To use it, first, highlight your names in column A and activate the macro, it will ask you to highlight the column you want to put the new values into.

**codepin** · 03-19-2009, 04:19 AM

Originally Posted by zbor

When you autofilter it copy just as anything else (select -> Ctrl+C) and paste (Ctrl+V)

Hi Zbor,

Sorry, I'm not sure what you mean. My first thread has 2 questions and they have no relation to each other. Q1 is how to make a list in column B, which contains each unique data in column A without repeating. Q2 is in case of an autofilter, how can I copy the data inside the autofilter drop-down box except the Sort Ascending / Sort Descending / All / Top 10 / Custom, and paste it somewhere (because that list inside the drop-down box is exactly the unique data list of column A, similar to my Q1).

My English is not very good but what my understanding is, you are replying my Q2, that it cannot be done. Kindly let me know if I'm wrong, many thanks.

Originally Posted by JBeaucaire

Not sure where this macro came from, but it's in my .bas catalog:

Please Login or Register  to view this content.

Put that macro into a standard module.

To use it, first, highlight your names in column A and activate the macro, it will ask you to highlight the column you want to put the new values into.

Hi JBeaucaire,

Thanks for your reply. But the file is not just for myself as this is used for many others. Our excel skill level varies and my boss prefers not to use any macros for everyone of us sake (and of course for his sake). Any other suggestion, please?

**Cheeky Charlie** · 03-19-2009, 04:22 AM

Crikey...

1st question:
Select column A data, go to Data-> filter -> advanced filter
You want to copy unique values only to another place ->B1

easy peasy!

I think this negates the second question(?) If not, please could you clarify that one?

HTH

**codepin** · 03-19-2009, 05:08 AM

Originally Posted by Cheeky Charlie

Crikey...

1st question:
Select column A data, go to Data-> filter -> advanced filter
You want to copy unique values only to another place ->B1

easy peasy!

I think this negates the second question(?) If not, please could you clarify that one?

HTH

Hi Cheeky Charlie,

YYYYYEEEEESSSSS!!!!! You have my full salute, many thanks.