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
5
289 Views
Last Modified: 2013-06-02
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
Comment
Question by:impala6
  • 2
  • 2
5 Comments
 
LVL 18

Assisted Solution

by:Raheman M. Abdul
Raheman M. Abdul earned 333 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 18

Assisted Solution

by:Raheman M. Abdul
Raheman M. Abdul earned 333 total points
ID: 39191166
Result file attached
Copy-of-Work.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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

Open in new window

0
 

Author Comment

by:impala6
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

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

Open in new window

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help inserting code to fix a excel bug 8 24
Excel 3 22
Name Rotation 11 28
Excel Difference between 2013 and 2016 2 5
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now