Comparing two cell in different spreadsheets

[Solved/Closed]
Report
Posts
4
Registration date
Wednesday October 24, 2012
Status
Member
Last seen
October 29, 2012
-
Posts
4
Registration date
Wednesday October 24, 2012
Status
Member
Last seen
October 29, 2012
-
Hello,

I'm currently struggling to do the following:

Spreadsheet A
Sheet1


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
Sheet1


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).

I wouldn't mind do it manually if the files were small, but I have hundreds of rows in each file.

I would appreciate is I could get some help!!!!


Regards,

Narley





2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Are you trying to match the names only or names and dates both needs to match for correct project ?
Posts
4
Registration date
Wednesday October 24, 2012
Status
Member
Last seen
October 29, 2012

Names and dates needs to match for the correct project. Example:

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


I need to mach the Names and Dates to the appropriated Project.
Posts
4
Registration date
Wednesday October 24, 2012
Status
Member
Last seen
October 29, 2012

Names and dates needs to match for the correct project. Example:

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


I need to mach the Names and Dates to the appropriated Project.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try this
Sample Call :
=projectLookup(A2,C2)

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
      
      Do
         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
            Else
               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) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
         
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         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) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function
Posts
4
Registration date
Wednesday October 24, 2012
Status
Member
Last seen
October 29, 2012

Thank you rizvisa1! The code was spot on!