[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Generating A Unique List

Posted on 2004-11-09
4
Medium Priority
?
227 Views
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.

Ron
0
Comment
Question by:ronaldj
  • 2
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Colosseo earned 1000 total points
ID: 12532862
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
  Next
 
  ' 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
  Next
 
  ' Select the output column
  Range("C1").Activate

  ' 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)
  Next

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

HTH, any questions just say

Scott
0
 

Author Comment

by:ronaldj
ID: 12533036
Scott,

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,
Ron
0
 
LVL 15

Expert Comment

by:Colosseo
ID: 12533094
0
 

Author Comment

by:ronaldj
ID: 12533242
Scott,

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

Ron

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
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…

864 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