There might be a way to do that with pivot tables, but as a programmer I'd use a macro routine to solve it.
First, create two new sheets (or rename unused sheets), named AddressChange and Workbench.
Select a cell on WorkBench and enter this code: =COUNTA(Sheet2!A:A) Then, with that cell selected, in the Defined Names section of the Formulas entry in the ribbon, click on Define Name, entering NameCount in the Name field and clicking OK.
Select a different cell on WorkBench and enter this code: =COUNTA(AddressChange!A:A) Repeat the steps described in the previous paragraph to define that cell with the name ChangeCount.
On the AddressChange sheet, put Last Name in cell A1, First Name in cell B1, Old Address in Cell B1, and New Address in cell D1.
In the Code section of the Developer entry in the ribbon, click on Visual Basic. Using the menu of the form that appears, choose Insert and then Module. Copy and paste the code below into the pane with the cursor:
Code:
Sub Checker
'
Dim cSht1 As String, cSht2 As String, cRng1 As String, cRng2 As String, lOldName As Long, lNewName As Long, lChange As Long
Dim cLastName1 As String, cFirstName1 As String, cLastName2 As String, cFirstName2 As String, fOK As Boolean
cSht1 = "Sheet2!"
cSht2 = "AddressChange!"
lNewName = 2
For lOldName = 2 To [NameCount]
Application.StatusBar = "Processing Name #" & lOldName - 1 & " out of " & [NameCount] - 1 & ": " & lChange - 1 & " address changes found"
cRng1 = CellName(cSht1 & "A", lOldName)
cLastName1 = Range(cRng1).Value
cRng1 = CellName(cSht1 & "B", lOldName)
cFirstName1 = Range(cRng1).Value
fOK = False
Do
cRng1 = CellName(cSht1 & "D", lNewName)
cLastName2 = Range(cRng1).Value
If cLastName1 = cLastName2 Then
cRng1 = CellName(cSht1 & "E", lNewName)
cFirstName2 = Range(cRng1).Value
If cFirstName1 = cFirstName2 Then fOK = True
End If
If Not fOK Then lNewName = lNewName + 1
Loop Until fOK
cRng1 = CellName(cSht1 & "C", lOldName)
cRng1 = Range(cRng1).Value
cRng2 = CellName(cSht1 & "F", lNewName)
cRng2 = Range(cRng2).Value
If cRng1 <> cRng2 Then
cRng1 = CellName(cSht2 & "A", lChange + 1)
Range(cRng1).Value = cFirstName1
cRng1 = CellName(cSht2 & "B", lChange)
Range(cRng1).Value = cLastName1
cRng1 = CellName(cSht2 & "C", lChange)
Range(cRng1).Value = cRng1
cRng1 = CellName(cSht2 & "D", lChange)
Range(cRng1).Value = cRng2
End If
Next
Application.StatusBar = False
End Sub
Function CellName(fCol1 As String, fRow1 As Variant, Optional fCol2 As Variant, Optional fRow2 As Variant) As String
'
' CellName Function
'
' Given a column (with possible worksheet prefix) and numeric row, returns the cell as a string
'
If IsMissing(fCol2) Or IsMissing(fRow2) Then
CellName = fCol1 & Trim(Str(fRow1))
Else
CellName = fCol1 & Trim(Str(fRow1)) & ":" & fCol2 & Trim(Str(fRow2))
End If
End Function
Then close the Visual Basic form, select the Macros option from the View section of the ribbon, and click on the Run button (Checker should be the 'highlighted', and only, selection). The routine will execute, essentially finding the name in the D and E columns that matches each name in the A and B columns, comparing their addresses, and, should the addresses differ, writing the name and both addresses on the AddressChange sheet.
The code assumes that both lists are in alphabetical order, and that each name in the A & B columns has a match in the D & E columns.