|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
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 |
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 |