Link to home
Create AccountLog in
Avatar of impala6
impala6Flag for United States of America

asked on

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
SOLUTION
Avatar of Raheman M. Abdul
Raheman M. Abdul
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

Avatar of impala6

ASKER

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
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account