Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Lists
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Lists
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. -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Lists
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 -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Lists
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 -----Original Message----- 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. -----Original Message----- 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare lists | Excel Discussion (Misc queries) | |||
compare two lists | Excel Worksheet Functions | |||
compare two lists | Excel Discussion (Misc queries) | |||
Compare two lists | Excel Discussion (Misc queries) | |||
Compare two lists | New Users to Excel |