Comparing two lists
 

Hi:

I have two lists in the same sheet side to side. List one has 80
values and list 2 has 400 values. Is there a sub that would compar
every value in list 1 against every value in list 2 and if the valu
does not exist in list 2, create another list with the values not foun
in list 2 ?

* _List_1_ *
2
3
4

Comparing two lists
 

'assume list 1 in col a, starting in row 2 with list 2 in col b an
output in col c
'I have not tested this code - it is adapted from code that work
though
'
Option Explicit
Sub Compare()
'
Dim baditem(1000)
Dim list1 As range
Dim list2 As Range
'
' Clear old Results
'
Range("c2:c1000").ClearContents
set list1 = range(cells(2 , 1).cells(2 , 1).end(xldown))
set list2 = range(cells(2 , 2).cells(2, 2).end(xldown))
list1end = range("list1").end(xldown).row
'
' Search on list 2 for items on list 1
'
i = 0
For j = 2 list1end
thisitem = Cells(j, 1).value
Set rng = Range("list2").Find(thisitem)
If rng Is Nothing Then GoTo baditem
goto nextj
baditem:
i = i + 1
baditem(i) = thisitem
nextj:
Next j
baditems = i
For m = 1 To baditem
Cells(m+1, 3).Value = baditem(m)
Next m
End Su

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=26966