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