Checking for duplicates Accounts before importing Contacts
Sometimes you need a quick way of importing Contacts from various events and you suspect that some of these Contacts already exist in ASPEC.
The fastest way to check for duplicates is to use vlookup function in Excel. This function checks if a value from one cell exits in another range of cells.
A duplicate Accounts are the ones that have the same Account Name. A duplicate Contacts are the ones that have the same Email address.
This post assumes that you have an file of Contacts for imports and that this file contains a column with Account Name and Contact Email.
Please follow these steps:
- Export data from ASPEC
Export Accounts. Make sure you have Account Name column and Account UUID column.
Export Contacts. Make sure you have Contact Email column.
2. Prepare exported data for duplication search
Create a new tab in your import file and copy there 2 columns: Account Name and UUID column.
Highlight all cells you just pasted, go to Data tab and click Sort.
Select Account Name as the sorting column.
Fig: Adding Account information to a new tab in import file
Highlight all rows the 2 columns and Define Range.
Fig: Selecting a range
Give the range the name,
Fig: Naming a range
3. Check which Accounts exist in ASPEC
Now it’s time to fill the Account UUID column in the main import tab. To do that, use vlookup function that will be checking if the Account name existed in the list you just copied and named in the new tab.
In the Account UUID cell in row 2 enter the following formula:
=vlookup(f2, Accts,2, false)
Account name is the import file is entered in cell F2 and the list of all ASPEC Accounts has a name “Accts”. The UUID in Accts is in column 2. You want the exact match, so the last parameter is set to false. If you want a partial match, then change that parameter to true.
Fig: Entering formula
Now drag the formula to the last row. Excel will auto-populate cells but will also change the cell reference in each row. The result in each cell in column “Account UUID” is UUID of Account existing in ASPEC or “#N/A” if the Account is not found.
Fig: Populating all UUID rows in the import file
You want to remove all “#N/A”. Blank cell tells the import to create a new Account.
To remove “#N/A” you need to replace vlookup with the value that Excel shows to you. Copy all values from column F and paste them as Values.
Fig: Copying and pasting as values
Now you can replace “#N/A” with an empty value.
Fig: Replacing value in a column
Cells that have UUID correspond to Accounts that exist in ASPEC.
4. Find duplicate Contacts by looking up email address
Create another new tab in the import file.
Copy Contact Email, Last Name and UUID into that tab. Similarily as in Accounts, sort this range by Contact Email and give this range a name.
Insert a new column next to Contact Email in the import tab.
Enter vlookup formulas to check if Contact Email exists in ASPEC. To do that, vlookup will check the Contact range.
Fig: Checking for existing email address
If the cell value is not “#N/A” then the email exists in the lookup range. Therefore, ASPEC contains a Contact with that email.