Answers.Body: <p>The most reliable way to do this is to not import addresses that are known matches.</p>
  
  <p>I regularly use <a href="http://www.pentaho.com/product/data-integration" rel="nofollow noreferrer">Pentaho Kettle</a> to create import files for me.  I'll separate addresses into their own CSV, then use Kettle to look up whether the address matches the contact's address in Civi.  I'll filter out those that do, leaving only remaining addresses.  However, Kettle has a high learning curve.</p>
  
  <p>You could do the same with just LibreOffice Calc or Microsoft Excel - but it requires some Excel/Calc knowledge.</p>
  
  <ul>
  <li>Export all CiviCRM addresses (with external identifier) to a CSV.</li>
  <li>Open the CSV in Calc/Excel, and concatenate all the fields you want to compare into a new column (make this Column A).  Label this column "Unique lookup".</li>
  <li>Create a concatenated "Unique lookup" column in the new address spreadsheet as well, using the same fields.</li>
  <li>On the <em>new</em> address sheet, Use the <code>VLOOKUP</code> function in Excel, using exact matching.  Use the <em>existing</em> addresses as your lookup sheet.  Return a value in a new column for matching addresses.</li>
  <li>Sort by the <code>VLOOKUP</code> field in your new addresses.  Delete any address that has a match in the existing addresses.</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-04T05:54:44.903Z
Answers.LastActivityDate: 2017-10-04T13:58:08.893Z
Answers.LastEditDate: 2017-10-04T13:58:08.893Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>I have set of name, addresses, phone number etc in CiviCRM. These are slightly outdated. I have an excel sheet for the same people with their new addresses. I have a unique external identifier for most of them.</p>
  
  <p>If the excel sheet address is new, I want CiviCRM to add it as a second address and if it is same as old address, then I don't want to update. </p>
  
  <p>How can I achieve this? </p>
  
Tags: <contacts><duplicate-contacts><addresses><upgrade>
Title: Comparing Duplicate address
ThreadText: # Comparing Duplicate address
  
  (Asked by: username_0 on 2017-10-04)
  
  I have set of name, addresses, phone number etc in CiviCRM. These are slightly outdated. I have an excel sheet for the same people with their new addresses. I have a unique external identifier for most of them.
  
  If the excel sheet address is new, I want CiviCRM to add it as a second address and if it is same as old address, then I don't want to update.
  
  How can I achieve this?
  
  **Tags:** contacts, duplicate-contacts, addresses, upgrade
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-10-04. Score: 3 | Accepted answer
  
  The most reliable way to do this is to not import addresses that are known matches.
  
  I regularly use [Pentaho Kettle](http://www.pentaho.com/product/data-integration) to create import files for me. I'll separate addresses into their own CSV, then use Kettle to look up whether the address matches the contact's address in Civi. I'll filter out those that do, leaving only remaining addresses. However, Kettle has a high learning curve.
  
  You could do the same with just LibreOffice Calc or Microsoft Excel - but it requires some Excel/Calc knowledge.
  
  * Export all CiviCRM addresses (with external identifier) to a CSV.
  * Open the CSV in Calc/Excel, and concatenate all the fields you want to compare into a new column (make this Column A). Label this column "Unique lookup".
  * Create a concatenated "Unique lookup" column in the new address spreadsheet as well, using the same fields.
  * On the *new* address sheet, Use the `VLOOKUP` function in Excel, using exact matching. Use the *existing* addresses as your lookup sheet. Return a value in a new column for matching addresses.
  * Sort by the `VLOOKUP` field in your new addresses. Delete any address that has a match in the existing addresses.
  
  
  ---
  
  