Solved

# Column A has a list of values with blanks and duplicate values.  How can I create a unique list of values in Column A in Column B?

Posted on 2013-05-23
Medium Priority
334 Views
I need a macro to go through a list of values in Column A and put the distinct values from this list into Column B.
Work.xlsm
0
Question by:impala6
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2
• 2

LVL 19

Assisted Solution

Raheman M. Abdul earned 1332 total points
ID: 39191152
Select column A
Click Data-> Advanced Filter
Select "Copy to another location" and select Column B
Tick on "Unique Records only" then click OK
0

LVL 19

Assisted Solution

Raheman M. Abdul earned 1332 total points
ID: 39191166
Result file attached
Copy-of-Work.xlsm
0

LVL 43

Expert Comment

ID: 39191167
``````Sub listunique()
Rows("1:1").Insert
[a1] = "List"
Range("A1:A" & Rows.Count).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=True
Rows("1:1").Delete
End Sub
``````
0

Author Comment

ID: 39191443
The Advanced Filter seemed to work; however, when I used it for the "Prod-Versions" sheet in this attachment, it is easy to see that it removed duplicates, but it left a lot of values out of the list it built in Columns C:D.  For instance, the value of "RDC" does not appear.

How do I evaluate what is in columns A and B and present the unique values in D and C? The Advanced Filter seems to be leaving a lot of records behind.
Work.xlsm
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 668 total points
ID: 39192284
``````Sub listunique()
Range("A1:b" & Rows.Count).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("c1:D1"), Unique:=True
End Sub
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month12 days, 10 hours left to enroll

#### 650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.