If I wanted to avoid duplicates, a Collection is one way to go.

But since they're books that are checked out, wouldn't you want to show multiple
copies being checked out?

John Walkenbach shows how to use a collection to get a unique list. He also
shows how that list can be sorted (you may want to do that):
http://j-walk.com/ss/excel/tips/tip47.htm

ll wrote:

Dave,
Thanks for your help - this is working ideally. I see that the multi
select is also activated in the listbox. Very handy!
I had come up with a slightly different script, based in a module with
a collection, which also worked.
What would be an advantage of an array vs collection (is it the
possibility of duplicate values within the array)?

Thanks

Dave Peterson wrote:
I'd just use an array. I put this behind a userform that had a listbox on it:

Option Explicit
Private Sub UserForm_Initialize()
Dim CompareRange1 As Range
Dim x1 As Range
Dim CompareRange2 As Range
Dim res As Variant
Dim myArr() As String
Dim iCtr As Long

With Worksheets("sheet1")
Set CompareRange1 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Worksheets("Sheet2")
Set CompareRange2 = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

' Loop through each cell in the selection and compare it to
' each cell in CompareRange.

ReDim myArr(1 To CompareRange1.Cells.Count)
iCtr = 0
For Each x1 In CompareRange1
res = Application.Match(x1, CompareRange2, 0)
If IsError(res) Then
'missing
iCtr = iCtr + 1
myArr(iCtr) = x1.Value
End If
Next x1

If iCtr = 0 Then
With Me.ListBox1
.AddItem "No Mismatches"
.Enabled = False
End With
Else
ReDim Preserve myArr(1 To iCtr)
With Me.ListBox1
.List = myArr
.Enabled = True
.MultiSelect = fmMultiSelectMulti
End With
End If

End Sub

ll wrote:

Dave,
Thanks - I had the range still on only 5 cells, so for some "strange"
reason it was only returning 5! lol
One more point - as I am wanting to get the results into an Excel VBA
form, will the results go easily into a collection?

Thanks
Dave Peterson wrote:
For the code I suggested, if you point comparerange1 to your total list of books
and point comparerange2 to the list of books checked out, what happens?

ll wrote:

Thanks for your help - both of these return some results, but allow me
to clarify what I am trying to achieve:
I need to compare the data in range A with that or range B. It will be
a library listing of books with no identical entries. Range A will
come from the list of books which have been checked out, while Range B
will be the complete listing of books. A comparison of the two will
(hopefully) produce a list of books not checked out, which can populate
a VB Excel Form.
What type of solution would produce the complete list of
not-checked-out books?

Thanks,
Louis

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson