swjtx99
asked on
Create list of unique values/strings from one sheet to another
I have 40K rows in sheet1 column C but only ~75 unique values/strings in that column.
I need the unique list to be copied to Sheet2 row 1.
I would prefer not to copy all/sort by unique, rather copy only unique values, ie. look in sheet1 column C, get unique values and copy to sheet2, starting at column A row 2.
Has to be VBA (macro).
Thanks in Advance,
swjtx99
I need the unique list to be copied to Sheet2 row 1.
I would prefer not to copy all/sort by unique, rather copy only unique values, ie. look in sheet1 column C, get unique values and copy to sheet2, starting at column A row 2.
Has to be VBA (macro).
Thanks in Advance,
swjtx99
you can use the advancedfilter to do this. Click the Unique Values checkbox.
Do you want vba or dynamic formula?
ASKER
It needs to be VBA. I can do this already with advancedfilter/unique values and with a dynamic formula but I am trying to automate the process.
Attached is an example File. Sheet1 is an example data set and sheet2 is what I wantExample2.xlsx the VBA to create.
Attached is an example File. Sheet1 is an example data set and sheet2 is what I wantExample2.xlsx the VBA to create.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello,
here is the fast and short code.
here is the fast and short code.
Sub Extractunique()
Dim a, e
With Sheets("Sheet1")
a = .Range("C1", .Range("C" & Rows.Count).End(xlUp)).Value
End With
With CreateObject("Scripting.Dictionary")
.CompareMOde = 1
For Each e In a
If Not IsEmpty(e) Then .Item(e) = Empty
Next
Sheets("sheet2").Range("A2", Columns("A").SpecialCells(xlCellTypeLastCell)).ClearContents
Sheets("sheet2").Range("A2").Resize(.Count).Value = Application.Transpose(.keys)
End With
End Sub
@ProfessorJimJam
With a dictionary object, you should use its .EXISTS() method.
With a dictionary object, you should use its .EXISTS() method.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Thanks to you both. ProfessorJimJam's worked better, however the transpose at the end doesn't seem to work.
Regards,
swjtx99
Thanks to you both. ProfessorJimJam's worked better, however the transpose at the end doesn't seem to work.
Regards,
swjtx99
@swjtx99
You should work with the experts until you have a working solution. Should we reopen the question for further dialog?
doesn't seem to workWhich transpose doesn't work?
You should work with the experts until you have a working solution. Should we reopen the question for further dialog?
@swjtx99
what do you mean the transpose does not work?
check your original question. your question do not state to transpose the the list.
if you are referring to line 12 of the code Application.Transpose(.key s)
this is not the Transpose you use in built excel function. This Transpose has a different functionality.
try to to remove it and instead just put Sheets("sheet2").Range("A2 ").Resize( .Count).Va lue = .keys
then see you get a wrong result and this is the power of Application.Transpose(.key s) that make the code do what you asked for.
if you are interested to learn how to use Dictionary-Class-in-VBA then in Experts Exchange forum, there is a very good article written by Patrick Matthews
check it here
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html
i believe what you got what you asked for , in this thread. if you have any other question. you may need to open a new question.
regards,
Professor
what do you mean the transpose does not work?
check your original question. your question do not state to transpose the the list.
if you are referring to line 12 of the code Application.Transpose(.key
this is not the Transpose you use in built excel function. This Transpose has a different functionality.
try to to remove it and instead just put Sheets("sheet2").Range("A2
then see you get a wrong result and this is the power of Application.Transpose(.key
if you are interested to learn how to use Dictionary-Class-in-VBA then in Experts Exchange forum, there is a very good article written by Patrick Matthews
check it here
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html
i believe what you got what you asked for , in this thread. if you have any other question. you may need to open a new question.
regards,
Professor