Event Organiser - Mailing list - Remove dulicates

Guide to Remove Duplicates and Prepare Import List in Excel

This guide will help you merge two lists of email addresses in Excel, remove duplicates, and create a clean import list with names and emails (no headings) for our system. Follow these steps carefully.

Step 1: Prepare Your Excel File

1. Open Microsoft Excel and create a new workbook or use an existing one.
2. Organize your data:
   • Place the first list of names in Column A, starting from cell A2.
   • Place the corresponding email addresses in Column B, starting from B2.
   • Place the second list of names in Column C, starting from C2.
   • Place the second list of email addresses in Column D, starting from D2.

   Example layout:

   A	B	C	D
   Name List 1	Email List 1	Name List 2	Email List 2
   John	[e-mail]	Jane	[e-mail]
   Jane	[e-mail]	Bob	[e-mail]

3. Clean the data:
   • Ensure no extra spaces in emails. Select Columns B and D, go to Data Text to Columns Finish (this helps trim spaces).
   • Remove blank rows or empty cells in your lists.

Step 2: Merge the Two Lists

1. Create a merged list:
   • In Column E, label cell E1 as "Merged Names" (optional).
   • In Column F, label cell F1 as "Merged Emails" (optional).
   • Copy all names from Column A (e.g., A2:A100) and paste into E2 onward.
   • Copy corresponding emails from Column B (e.g., B2:B100) and paste into F2 onward.
   • Copy all names from Column C and paste below the last name in Column E.
   • Copy all emails from Column D and paste below the last email in Column F, ensuring names and emails stay aligned.
2. Verify the merged list: Ensure each name in Column E has a corresponding email in Column F.

Step 3: Highlight and Remove Duplicates

1. Select the email column: Highlight all cells in Column F with merged emails (e.g., F2:F200).
2. Highlight duplicates:
   • Go to the Home tab Conditional Formatting Highlight Cells Rules Duplicate Values.
   • Choose a highlight color (e.g., Light Red) and click OK.
   • Emails appearing more than once (in both original lists) will be highlighted.
3. Remove duplicates:
   • Select Columns E and F (both names and emails).
   • Go to Data Remove Duplicates.
   • In the dialog box, ensure only the "Merged Emails" column (F) is checked, as we want to remove duplicates based on emails only.
   • Click OK. Excel will remove duplicate emails, keeping the first occurrence and its corresponding name.
4. Verify the clean list: Check Columns E and F to ensure no duplicate emails remain and each email has a name.

Step 4: Prepare the Import List

1. Create the final list:
   • In a new sheet (click the "+" at the bottom to add a sheet), copy the cleaned names from Column E (e.g., E2:E100) and paste into A1.
   • Copy the cleaned emails from Column F and paste into B1.
2. Remove headings: Ensure there are no headings (e.g., "Merged Names" or "Merged Emails") in the new sheet. The list should start with data in A1 and B1.
3. Final format example:

   John	[e-mail]
   Jane	[e-mail]
   Bob	[e-mail]

Step 5: Save and Export

1. Save the file:
   • Go to File Save As.
   • Save as an Excel file (e.g., Clean_Import_List.xlsx).
2. Export for import (if needed):
   • If the system requires a CSV file, go to File Save As, choose CSV (Comma delimited), and save.
   • Open the CSV in a text editor to verify it contains only names and emails, separated by commas, with no headings.
3. Send the file: Share the Excel or CSV file with the event team for import into the system.

Notes:

• Backup: Save a copy of your original file before making changes.
• Excel Version: This guide works for Excel 2010 and later (including Microsoft 365). Menu options may vary slightly in older versions.
• Case Sensitivity: Excel treats "[e-mail]" and "[e-mail]" as the same. If case matters, contact the event team.
• Large Lists: For thousands of emails, the process is the same, but allow extra time for Excel to process.
• Help: If you encounter issues, contact the event team for support.