Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-21-2010, 11:51 AM
spartanhockey spartanhockey is offline 2 sets of names and adresses. need to know which addresses have changed. (Picture) Windows XP 2 sets of names and adresses. need to know which addresses have changed. (Picture) Office 2007
Novice
2 sets of names and adresses. need to know which addresses have changed. (Picture)
 
Join Date: Jan 2010
Posts: 3
spartanhockey is on a distinguished road
Default 2 sets of names and adresses. need to know which addresses have changed. (Picture)





The list on the left has about 1600 names
The list on the right has about 1900 names, which contain all of the list on the left names plus about 300 extra scattered throughout.

Because of this I cant simply sort A-Z and and line up the names and see if the addresses are different.

Im wondering if theres a function that will compare the two last name cells and see if the address for the last name on the left is the same as the address for the last name on the right.

Thank you
Reply With Quote
  #2  
Old 01-25-2010, 11:14 AM
CGM3 CGM3 is offline 2 sets of names and adresses. need to know which addresses have changed. (Picture) Windows XP 2 sets of names and adresses. need to know which addresses have changed. (Picture) Office 2003
Advanced Beginner
 
Join Date: Oct 2009
Posts: 38
CGM3 is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 01-25-2010, 11:21 AM
spartanhockey spartanhockey is offline 2 sets of names and adresses. need to know which addresses have changed. (Picture) Windows XP 2 sets of names and adresses. need to know which addresses have changed. (Picture) Office 2007
Novice
2 sets of names and adresses. need to know which addresses have changed. (Picture)
 
Join Date: Jan 2010
Posts: 3
spartanhockey is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 01-25-2010, 02:09 PM
CGM3 CGM3 is offline 2 sets of names and adresses. need to know which addresses have changed. (Picture) Windows XP 2 sets of names and adresses. need to know which addresses have changed. (Picture) Office 2003
Advanced Beginner
 
Join Date: Oct 2009
Posts: 38
CGM3 is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 01-26-2010, 10:12 AM
spartanhockey spartanhockey is offline 2 sets of names and adresses. need to know which addresses have changed. (Picture) Windows XP 2 sets of names and adresses. need to know which addresses have changed. (Picture) Office 2007
Novice
2 sets of names and adresses. need to know which addresses have changed. (Picture)
 
Join Date: Jan 2010
Posts: 3
spartanhockey is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 01-27-2010, 07:03 AM
CGM3 CGM3 is offline 2 sets of names and adresses. need to know which addresses have changed. (Picture) Windows XP 2 sets of names and adresses. need to know which addresses have changed. (Picture) Office 2003
Advanced Beginner
 
Join Date: Oct 2009
Posts: 38
CGM3 is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 01-27-2010, 01:54 PM
BjornS BjornS is offline 2 sets of names and adresses. need to know which addresses have changed. (Picture) Windows Vista 2 sets of names and adresses. need to know which addresses have changed. (Picture) Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Attached Files
File Type: xls Changed addresses.xls (19.0 KB, 13 views)
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:20 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft