Forum Discussion
Bulli90
Jan 28, 2025Copper Contributor
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 description of the requirements:
Mapping of Microsoft master data to our CompanyC and CompanyB master data. Thousands of records(5k+).
Mapping phase:
1. after the ‘vlookup’ phases
2. then use Excel's fuzzy logic tool, which does another mapping of about 50%.
3. finally, the rest requires some skills in model building or automation or Excel tool:
Logic to create:
*If account names are similar, check if regions are the same, then check if BRANCHES are the same = 90% Confidence map.
*For the rest, IF REGIONS are the same AND BRANCHES are the same, then HOW similar the names are = recommended mapping with 60% confidence.
4 Finally, complete manually
dummy data
Thanks for help :)
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.
- VLOOKUP Phase: