Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

Excel 2003 Compare & Match

  • Please log in to reply



    New Member

  • Member
  • Pip
  • 1 posts
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.
  • 0





  • Member
  • PipPip
  • 13 posts

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,
  • 0

Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP