Excel - Comparing two cells from different spreadsheets

October 2016


Consider the following situation:
Spreadsheet A

Name Expenses Date Project 
John 10 23/10/2011 (Get data from Spreadsheet B) 
Mark 50 12/5/2011 (Get data from Spreadsheet B) 
Patricia 34 4/11/2011 (Get data from Spreadsheet B) 
Anthony 88 19/3/2011 (Get data from Spreadsheet B) 
Mark 20 5/7/2011 (Get data from Spreadsheet B) 

Spreadsheet B

Name Project Date 
Mark Painting 12/5/2011 
Anthony Singing 19/3/2011 
Patricia Teaching 4/11/2011 
Mark Reading 5/7/2011 
John Painting 23/10/2011 

What I need is to get the name of the project from Spreadsheet B (sheet1) and add to the column PROJECT in Spreadsheet A (sheet1). How to automate this process?


Try this
Sample Call :

A2= Cell in which name is found 
C2= cell in which date is found 
Sheet1: name where the information about project is present 

Option Explicit

Public Function projectLookup(nameCell As Range, dateCell As Range) As String

   Dim sourceSheet         As String
   Dim nameRow             As Long
   Dim maxRows             As Long
   sourceSheet = "Sheet1"
   projectLookup = vbNullString
   With Sheets(sourceSheet)
      maxRows = getItemLocation("*", .Cells)
      If maxRows = 0 Then Exit Function
      nameRow = 1
         nameRow = getItemLocation(nameCell.Value, .Range(.Cells(nameRow, "A"), .Cells(maxRows, "A")), bLastOccurance:=False)
         If (nameRow > 0) Then
            If (.Cells(nameRow, "C") = dateCell) Then
               projectLookup = .Cells(nameRow, "B")
               nameRow = 0
            ElseIf (nameRow = maxRows) Then
               nameRow = 0
               nameRow = nameRow + 1
            End If
         End If
      Loop While (nameRow <> 0)
   End With
End Function

Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
   'find the first/last row/column  within a range for a specific string
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
   If (bFullString) _
      iLookAt = xlWhole
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
      iSearchDir = xlPrevious
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
      iSearchOdr = xlByColumns
      iSearchOdr = xlByRows
   End If
   With rngSearch
      If (bLastOccurance) _
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
      getItemLocation = Cell.Column
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function

Thanks to rizvisa1 for this tip.

Related :

This document entitled « Excel - Comparing two cells from different spreadsheets » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.