Answers.Body: <p>If you have already imported all your contacts, then each one will have a unique CiviCRM ID. As you only have 150 memberships to take care of, it won't be a huge task to match the CiviCRM ID to the membership record (and with some Excel skills, which I don't have) it could probably be at least semi-automated. Then you can use the CiviCRM ID as the match field for your membership records.</p>
  
  <p><a href="http://book.civicrm.org/user/current/common-workflows/importing-data/" rel="nofollow">http://book.civicrm.org/user/current/common-workflows/importing-data/</a> states that the CiviCRM ID or an external ID will do the job.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-03-28T00:18:54.233Z
Answers.LastActivityDate: 2015-03-28T00:18:54.233Z
Answers.UserDisplayName: Graham
Answers.Body: <p>Here's an idea: </p>
  
  <ol>
  <li><p>Import contacts (as you have already done)</p></li>
  <li><p><em>Export</em> the contacts you just imported, selecting the at least the following fields: contact ID, first name, last name</p></li>
  <li><p><strong>Use spreadsheet formulas</strong> to match the ID up like this:</p>
  
  <ol>
  <li><p>Add a new sheet in your memberships spreadsheet</p></li>
  <li><p>Paste in the exported data</p></li>
  <li><p>Add a new column to the <em>left</em> (can't be right) of your exported data, and call this column "name". </p></li>
  <li><p>Create and fill a formula for the name column to concatenate the first and last name (e.g. <code>=C2&amp;" "&amp;D2</code> will concatenate the contents of cells C2 and D2, with a space in between.) </p></li>
  <li><p>In your memberships sheet, create a new column called id, and create a formula like <code>=VLOOKUP(A2,Sheet2.A$2:D$10000,2,0)</code>. </p>
  
  <ol>
  <li><p><code>A2</code> is for the column in your memberships sheet that contains the full name. If you have two columns, then you'd want to write something like <code>C2&amp;" "&amp;D2</code>, as we did before. </p></li>
  <li><p><code>Sheet2.A$2:D$10000</code> is an absolute reference (dollar signs so it won't change when formula is filled downwards) to the range containing all the ID reference data. I wrote 10000, but make sure you use a number large enough to encompass the whole data set. Note that <code>Sheet2</code> is the name of the sheet. </p></li>
  <li><p><code>2</code> is to say that the value you want to pull into your memberships sheet is the <em>second</em> column from the left in your ID data. </p></li>
  <li><p><code>0</code> is to say that you want the VLOOKUP function to give you "#NA" if it can't find a match instead of the (terribly chosen) default behavior of picking the nearest match. </p></li>
  </ol></li>
  <li><p>Export memberships spreadsheet to CSV, and then try importing into Civi using the ID values that you pulled in</p></li>
  </ol></li>
  </ol>
  
  <p><em>(These formulas should be correct for LibreOffice, but may need to tweaking for MS Office, with which I'm less familiar.)</em> </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-03-27T02:35:43.713Z
Answers.LastActivityDate: 2015-03-27T02:41:17.983Z
Answers.LastEditDate: 2015-03-27T02:41:17.983Z
Answers.UserDisplayName: Sean
Body: <p>We are transitioning a small organization to CiviCRM 4.5, with about 2000 total contacts and about 150 memberships. The contacts imported without any problem, but when we went to import the memberships, we found that CiviCRM required either an e-mail address or an external ID.</p>
  
  <p>About half of the members do not have an email address, and since the old membership system was a combination of Excel files and offline physical media, they have no unique membership numbers or other identifiers. All of the firstname+lastname combinations are unique, so we thought that should be sufficient, and we have the deduplication rule set to that effect elsewhere, but it seems to be inadequate for membership imports.</p>
  
  <p>We don't want to be in the business of creating dummy email addresses, so it seems we must manually assign an external ID to the old contact list <em>and</em> the membership list, then reimport both in turn. But is there an easier workaround— some way to use Firstname+Lastname as the unique identifier? Or is there another "best practice" we could adopt here?</p>
  
Tags: <civimember><import>
Title: How can I import Memberships without e-mail addresses or external IDs?
ThreadText: # How can I import Memberships without e-mail addresses or external IDs?
  
  (Asked by: username_0 on 2015-03-27)
  
  We are transitioning a small organization to CiviCRM 4.5, with about 2000 total contacts and about 150 memberships. The contacts imported without any problem, but when we went to import the memberships, we found that CiviCRM required either an e-mail address or an external ID.
  
  About half of the members do not have an email address, and since the old membership system was a combination of Excel files and offline physical media, they have no unique membership numbers or other identifiers. All of the firstname+lastname combinations are unique, so we thought that should be sufficient, and we have the deduplication rule set to that effect elsewhere, but it seems to be inadequate for membership imports.
  
  We don't want to be in the business of creating dummy email addresses, so it seems we must manually assign an external ID to the old contact list *and* the membership list, then reimport both in turn. But is there an easier workaround— some way to use Firstname+Lastname as the unique identifier? Or is there another "best practice" we could adopt here?
  
  **Tags:** civimember, import
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2015-03-27. Score: 5 | Accepted answer
  
  Here's an idea:
  
  1. Import contacts (as you have already done)
  2. *Export* the contacts you just imported, selecting the at least the following fields: contact ID, first name, last name
  3. **Use spreadsheet formulas** to match the ID up like this:
  
     1. Add a new sheet in your memberships spreadsheet
     2. Paste in the exported data
     3. Add a new column to the *left* (can't be right) of your exported data, and call this column "name".
     4. Create and fill a formula for the name column to concatenate the first and last name (e.g. `=C2&" "&D2` will concatenate the contents of cells C2 and D2, with a space in between.)
     5. In your memberships sheet, create a new column called id, and create a formula like `=VLOOKUP(A2,Sheet2.A$2:D$10000,2,0)`.
  
        1. `A2` is for the column in your memberships sheet that contains the full name. If you have two columns, then you'd want to write something like `C2&" "&D2`, as we did before.
        2. `Sheet2.A$2:D$10000` is an absolute reference (dollar signs so it won't change when formula is filled downwards) to the range containing all the ID reference data. I wrote 10000, but make sure you use a number large enough to encompass the whole data set. Note that `Sheet2` is the name of the sheet.
        3. `2` is to say that the value you want to pull into your memberships sheet is the *second* column from the left in your ID data.
        4. `0` is to say that you want the VLOOKUP function to give you "#NA" if it can't find a match instead of the (terribly chosen) default behavior of picking the nearest match.
     6. Export memberships spreadsheet to CSV, and then try importing into Civi using the ID values that you pulled in
  
  *(These formulas should be correct for LibreOffice, but may need to tweaking for MS Office, with which I'm less familiar.)*
  
  
  ---
  
  ### Answer by username_1 on 2015-03-28. Score: 1
  
  If you have already imported all your contacts, then each one will have a unique CiviCRM ID. As you only have 150 memberships to take care of, it won't be a huge task to match the CiviCRM ID to the membership record (and with some Excel skills, which I don't have) it could probably be at least semi-automated. Then you can use the CiviCRM ID as the match field for your membership records.
  
  <http://book.civicrm.org/user/current/common-workflows/importing-data/> states that the CiviCRM ID or an external ID will do the job.
  
  
  ---
  
  