Posted 30 July 2005 - 09:50 AM
I wasn't sure if you were speaking about VB Project or project list in your sheets. According to the formulas you posted, I guess it's the latter.
If you don't mind trying VBA, try this for comparing the old list to the new list:
(The code assumes it's run from the New file)
Dim wbOld As Workbook, wbNew As Workbook
Dim cl As Range, clFound
Set wbNew = ThisWorkbook
Set wbOld = Workbooks("ForCompareOld.xls") 'use your name here, MUST BE OPENED
For Each cl In wbOld.Sheets("Sheet1").Range("A2:A" & wbOld.Sheets("Sheet1").[a65536].End(3).Row)
Set clFound = wbNew.Sheets("Sheet1").[a:a].Find(cl.Value, LookAt:=xlWhole)
If clFound Is Nothing Then
strNF_New = strNF_New & cl.Value & ","
'now, strNF_New contains your list, you can dump it onto a worksheet, e.g.
y = Split(strNF_New, ",")
.[A1] = "not found"
.[A2].Resize(UBound(y)) = Application.Transpose(y)