• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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?

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
impala6
Asked:
impala6
  • 2
  • 2
3 Solutions
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
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
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Result file attached
Copy-of-Work.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
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
 
impala6Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Sub listunique()
    Range("A1:b" & Rows.Count).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("c1:D1"), Unique:=True
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now