• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • 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. AbdulCommented:
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. AbdulCommented:
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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