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

spartanhockey · #1 01-25-2010, 11:21 AM

THank you. That seems really complicated but I will work through it step by step later. You said it assumes that there is a match for every column. That isnt the case though because I have 300 extra names in one of my columns. So will this not work?

Or even more simply, is there anyway to just figure out which names in one column are not in the other column? and then I will just delete them

CGM3 · #2 01-25-2010, 02:09 PM

Sorry, I meant that it expects every name in Columns A and B to have an equivalent in Columns D and E. So if, say, A36 has Kent and B36 has Clark, there should be a pair in D and E, on some row, with the same values.

Your description of the data indicates this is so, but I thought I should mention it explicitly.

spartanhockey · #3 01-26-2010, 10:12 AM

I got this error

Run-time error '1004':

Method 'Range' of object'_Global' failed

I clicked debug and it highlighted this part in yellow on code

cLastName2 = Range(cRng1).Value

Im not sure what any of this means so Im hoping someone can help. Thanks

CGM3 · #4 01-27-2010, 07:03 AM

It means I made an error in the code that has it refering to "Row Zero". Replace the Checker routine (using copy and paste) with--

Code:

Sub Checker()
'
Dim cSht1 As String, cSht2 As String, cRng0 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
         cRng0 = CellName(cSht2 & "A", [ChangeCount] + 1)
         Range(cRng0).Value = cLastName1
         cRng0 = CellName(cSht2 & "B", [ChangeCount])
         Range(cRng0).Value = cFirstName1
         cRng0 = CellName(cSht2 & "C", [ChangeCount])
         Range(cRng0).Value = cRng1
         cRng0 = CellName(cSht2 & "D", [ChangeCount])
         Range(cRng0).Value = cRng2
      End If
   Next
   Application.StatusBar = False
 
End Sub

--and give it a try.

BjornS · #5 01-27-2010, 01:54 PM

Hi,
I have a suggestion of a formula way to solve this. The advantage is that other persons can better understand your worksheet. See attached example.

Short explanation (actually quite self-explaining if you look at the formulas):

To the left of each table, create o formula for a match key (last name+ first name). I have named the tables (Insert ... Define .. Name) "table1" and "table2", by marking the complete columns, not only until the last row filled with data, since there will be more data in the future.

The formulas in cell E and F does the whole check. If you don't want to see the new address, you can compress these formulas in E and F into one column. As an extra benefit, there is a check for new records with the help of the formula in column M.

I used conditional formatting to highlight changed / new records.

Kind regards
Bjorn

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