Forum Discussion
Data mapping.
Hi Experts, I come to ask if someone can explain what I am supposed to achieve based on the requirements, based on the attached sample data. I don't have much experience in excel. O is the descript...
Try below:
- VLOOKUP Phase:
- Start by using the VLOOKUP function to match the Microsoft master data with your CompanyC and CompanyB master data. This will help you identify and map exact matches.
- Fuzzy Logic Tool:
- After the initial VLOOKUP, use Excel's fuzzy logic tool (available in Excel's Data Analysis Toolpak) to find similar but not exact matches. This tool can match about 50% of the remaining records based on similarity.
- Advanced Logic and Automation:
- For the remaining data, you’ll need to apply more advanced logic and potentially some automation. You can use Excel functions and tools like IF, AND, and TEXT functions along with VBA (Visual Basic for Applications) for automation.
- Here’s a step-by-step approach:
a. Create Confidence Mapping:
=IF(AND(SIMILARITY(AccountName1, AccountName2) > Threshold, Region1 = Region2, Branch1 = Branch2), "90% Confidence Map", "")
b. Recommended Mapping:
=IF(AND(Region1 = Region2, Branch1 = Branch2, SIMILARITY(AccountName1, AccountName2) > LowerThreshold), "60% Confidence Map", "")
The SIMILARITY function represents a hypothetical function to measure how similar two account names are. You may need to use a custom VBA function or a third-party add-in for this purpose.
- Manual Completion:
- Finally, review the remaining data manually to ensure accuracy. This step involves visually checking the records and making necessary adjustments.