2 sets of names and adresses. need to know which addresses have changed. (Picture)

CGM3 · #2 01-25-2010, 11:14 AM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
security question....ports changed	cduval04	Outlook	0	01-12-2010 10:44 PM
Outlook font for replies changed	Neetjean	Outlook	1	11-23-2009 01:37 PM
Finding the complements between the two data sets	psenku	Excel	2	08-17-2009 11:15 PM
How do I accept non-Western (non-latin) character sets	3rd floor	Outlook	1	02-10-2006 03:17 PM
One bar chart for 3 sets of data	rclloyd	Excel	1	02-03-2006 09:51 AM