Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Create list of unique values/strings from one sheet to another

Posted on 2014-10-10
10
1,017 Views
Last Modified: 2014-10-13
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
0
Comment
Question by:swjtx99
  • 4
  • 4
  • 2
10 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40374425
you can use the advancedfilter to do this.  Click the Unique Values checkbox.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40374532
Do you want vba or dynamic formula?
0
 

Author Comment

by:swjtx99
ID: 40374894
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.
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 100 total points
ID: 40374992
Here are the recorded commands packaged into a single user-defined function.
Sub Q_28535711()
    Dim wksSrc As Worksheet
    Dim wksTgt As Worksheet
    Dim rngSrc As Range
    Dim rngTgt As Range
    Dim lngRows As Long
    Set wksSrc = Sheets("Sheet1")
    Set wksTgt = Sheets("Sheet2")
    'copy unique values to sheet2
    wksSrc.Range(wksSrc.Range("A1"), wksSrc.Range("A1").End(xlDown)).AdvancedFilter _
                Action:=xlFilterCopy, CopyToRange:=wksTgt.Range("A1"), Unique:=True
    
    'transpose the data
    Set rngSrc = wksTgt.Range(wksTgt.Range("A2"), wksTgt.Range("A2").End(xlDown))
    lngRows = rngSrc.Rows.Count
    Set rngTgt = wksTgt.Range(wksTgt.Cells(1, 1), wksTgt.Cells(1, lngRows))
    rngTgt.Value = WorksheetFunction.Transpose(rngSrc)
    
    'clear the original unique data values
    rngSrc.ClearContents
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40375264
Hello,

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

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40375284
@ProfessorJimJam

With a dictionary object, you should use its .EXISTS() method.
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 400 total points
ID: 40375509
Thanks Aikimark,  for pointing it.  yes, i should have used .exists in stead of isempty

here is the revised 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 .exists(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

Open in new window

0
 

Author Closing Comment

by:swjtx99
ID: 40376218
Hi,

Thanks to you both. ProfessorJimJam's worked better, however the transpose at the end doesn't seem to work.

Regards,

swjtx99
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40376247
@swjtx99
doesn't seem to work
Which transpose doesn't work?

You should work with the experts until you have a working solution.  Should we reopen the question for further dialog?
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40376663
@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(.keys)

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).Value = .keys

then see you get a wrong result and this is the power of Application.Transpose(.keys)  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
http://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
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

861 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