Generating A Unique List

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ronaldjAuthor Commented:

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,
ronaldjAuthor Commented:

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


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.