Creating Excel Function: Locate Mismatched Email Domains using Master Key
In this article, we will discuss how to create an Excel function to locate mismatched email domains using a master key. This technique is particularly useful for tech support teams that deal with large databases of customer information.
Background
Email addresses are an essential part of modern communication. They are used for both personal and business purposes. However, maintaining an accurate email database can be a challenge. Email addresses can change, and typos can lead to mismatches. In this article, we will show you how to use Excel functions to identify and correct mismatched email domains using a master key.
Prerequisites
Before we begin, make sure you have the following:
- Two Excel sheets: one named "MasterKey" and the other named "DataSheet"
- The "MasterKey" sheet should have a column named "Correct Party Name" and "Email Domain"
- The "DataSheet" should have a column named "Party Name" and "Email Address"
Solution
Step 1: Create a helper column in "DataSheet" to extract email domains
First, we need to extract the email domains from the "Email Address" column in "DataSheet". We will create a helper column named "Email Domain" to do this.
Click on the first empty cell in the "DataSheet" sheet, and enter the following formula:
=LEFT(A2,FIND("@",A2,1)-1)
INDEX(INDIRECT("Sheet1!$A$1:$Z$"&ROW(A2:A&MAX(ROW(A:))+ROW(A2:A)-ROW(A2))),MID(A2,FIND("@",A2,1),LEN(A2)))
This formula uses the LEFT and FIND functions to extract the part of the email address before the "@" symbol. It then uses the INDEX and MID functions to find the corresponding email domain in the "MasterKey" sheet.
Step 2: Create a helper column in "MasterKey" to extract correct email domains
Next, we need to extract the correct email domains from the "Correct Party Name" column in "MasterKey". We will create a helper column named "Correct Email Domain" to do this.
Click on the first empty cell in the "MasterKey" sheet, and enter the following formula:
=IFERROR(RIGHT(B2,LEN(B2)-FIND(".",B2,LEN(B2)-1)-1)),B2)
INDEX(INDIRECT("Sheet1!$A$1:$Z$"&ROW(B2:B&MAX(ROW(B:))+ROW(B2:B)-ROW(B2))),MATCH(B2,INDIRECT("Sheet1!$A$1:$A$"&ROW(B2:B&MAX(ROW(B:))+ROW(B2:B)-ROW(B2))),0))
This formula uses the IFERROR and RIGHT functions to extract the part of the email domain after the last "." symbol. It then uses the INDEX and MATCH functions to find the corresponding email domain in the "DataSheet" sheet.
Step 3: Create a formula to locate mismatched email domains
Now, we can create a formula to locate mismatched email domains using the helper columns we created in the previous steps.
Click on an empty cell in "DataSheet" and enter the following formula:
=IF(ISBLANK(B2),"",IF(D2=E2,"Match Found: "&B2,"Mismatch Found: "&B2&" should be "&F2))
This formula uses the ISBLANK, IF, and IFERROR functions to check if the "Party Name" is blank. If it is, the cell will be empty. If not, it uses the IF function to check if the email domain in "DataSheet" matches the correct email domain in "MasterKey". If it does, the formula will display "Match Found: " followed by the Party Name. If it doesn't, it will display "Mismatch Found: " followed by the Party Name and the correct email domain.
In this article, we showed you how to create an Excel function to locate mismatched email domains using a master key. We used helper columns to extract email domains from both sheets and a formula to compare them. This technique is useful for tech support teams that deal with large databases of customer information and want to ensure their email databases are accurate.