Generating A Unique List

Posted on 2004-11-09
Last Modified: 2010-04-17
In MS XL I have

column "A" containing Mr. A, Mr.B, Mr.C, Mr. D and  Mr. E.
column "B" I have Mr. B, Mr. C and  Mr. E.

I'm trying to write a VBA routine that would place, in column "C" only those individuals NOT present in both lists (i.e., Mr. A, Mr. D)

Any help would be greatly appreciated.

Question by:ronaldj
    LVL 15

    Accepted Solution

    Hi there

    this code should do what you are after

    Sub getUniqueList()

      ' Create a dictionary object (this will be used to find the unique values)
      Set d = CreateObject("Scripting.Dictionary")

      ' Select the cells in column A
      Set r = Range("A1", Range("A1").End(xlDown))

      ' Add each cell in column A to the dictionary
      For Each individual In r.Cells
        d.Add individual.Value, individual.Value
      ' Select the cells in column B
      Set r = Range("B1", Range("B1").End(xlDown))

      ' For each cell in column B
      For Each individual In r.Cells
        ' If the individual is already in the dictionary (ie not unique) then remove it from the dictionary
        If d.exists(individual.Value) Then d.Remove individual.Value
      ' Select the output column

      ' Get the unique values from the dictionary object into an array
      tmpArray = d.keys
      ' Add each item in the array to column C
      For i = 0 To UBound(tmpArray)
        ActiveCell.Offset(i, 0).FormulaR1C1 = tmpArray(i)

      ' Tidy up
      Set r = Nothing
      Set d = Nothing
    End Sub

    HTH, any questions just say


    Author Comment


    Thanks a bunch. I'm not familiar with the dictionary object...any suggested references, curious?

    Totally divorced question: have you ever run into a situation in XL where a routine is correctly run but part of the spreadsheet gets "colored out" when the result appears?...

    Thanks again,
    LVL 15

    Expert Comment


    Author Comment


    Kinda tuff to describe...seeing is believing...but here goes...

    Sheet "x" is visible...
    a UserForm appears on workbook open...
    select "some" option on the UserForm and click "run" button...
    UserForm sometimes "colors out" i.e., the UserForm area contents disappears and is totally replaced by a (usually) tan colored area...
    most of the time the UserForm then reappears when routine is finished...
    however, I have found that when the task at hand is large (e.g., searching through a directory containing a huge amount of files, after the task is completed, results are presented BUT the User form never comes back and I'm left with this large tan area in the middle of my screen!...

    have set ScreenUpdating=False...

    most bizzare



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    JQuery Date Time picker not showing 29 82
    linearIn  challenge 23 54
    array220 challenge 8 28
    java  and programming certification ? 4 25
    RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
    I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now