[valade1]

I need to compare an existing spreadsheet to an updated spreadsheet. Each has the same format & construction. I want to perform two tasks: 1. Identify if all the projects that were on the "old" spreadsheet are also on the "new" spreadsheet, and 2. List all new projects on the "new" spreadsheet that were not on the "old" spreadsheet.

Each project has a unique number identifier that is contained on both spreadsheets.

What is the best way to complete the task. I have looked at the following formulas: =EXACT; =VLOOKUP, but keep getting the #VALUE! error.

Any assistance would be appreciated.

[Advertisements]

Hello,

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)

Sub CompareOldToNew()
Dim wbOld As Workbook, wbNew As Workbook
Dim strNF_InNew
Dim strNF_InOld
Dim cl As Range, clFound
Dim y
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 & ","
End If
Next cl
'
'now, strNF_New contains your list, you can dump it onto a worksheet, e.g.
y = Split(strNF_New, ",")

With Sheets("Sheet3")
.[A1] = "not found"
.[A2].Resize(UBound(y)) = Application.Transpose(y)
End With
End Sub

best regards,
martin

[KingMartin]

Hello,

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)

Sub CompareOldToNew()
Dim wbOld As Workbook, wbNew As Workbook
Dim strNF_InNew
Dim strNF_InOld
Dim cl As Range, clFound
Dim y
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 & ","
End If
Next cl
'
'now, strNF_New contains your list, you can dump it onto a worksheet, e.g.
y = Split(strNF_New, ",")

With Sheets("Sheet3")
.[A1] = "not found"
.[A2].Resize(UBound(y)) = Application.Transpose(y)
End With
End Sub

best regards,
martin