Compare Lists

  • Thread starter Thread starter David Willey
  • Start date Start date
D

David Willey

I could use some help with validating items in a list with VBA. What
I would like to do is compare List A and List B. If an item is in
List A but not in List B I would like to delete item from List A.

Thanks for any help available.

Regards,

David
 
I haven't got the code to hand but you can achieve your
requirement by using the Filter function .. see the VBA
help files for documentation and example. Filter also
copes with case sensitivity.
 
use the match function


eg

sub quicktest()
for rw = firstrow to lastrow' of 'A'
item = cells(rw,cl) ' cl is the column letter of A
if notmatched(item) then
cells(rw,cl=""
end if
next
end sub
function notmatched(item as string) as boolean
on error resume next
dim res
res = Application.WorksheetFunction.Match(item,range
("b1:b100")
if err.number= 0 then
' matched
notmatched = false
else
err.clear
notmatched = true
end if
end function


Patrick Molloy
Microsoft Excel MVP
 
Here's some sample code:

Sub VerifyA()
a = Split("Jan,Feb,Mar,Apr", ",")
b = Split("Jan,Mar,Apr,May", ",")
c = a
For i = LBound(a) To UBound(a)
If -1 = UBound(Filter(b, a(i), True,
vbTextCompare)) Then
c = Filter(a, a(i), False, vbTextCompare)
End If
Next
a = c
MsgBox Join(a) ' will keep Jan,Mar,Apr in a
End Sub
 
Back
Top