How we helped a Private Equity client in carrying out data analysis and mapping exercise

The Context

The client, a Private Equity firm based out of US, wanted the TresVista team to carry out a mapping exercise for ~4854 customers. For this, the client provided two datasets, the first comprising of customer names and the latter consisting of account/opportunity that compiled the information about the contract details such as date, fees, etc. The two datasets had to be mapped against each other to form an interlinked data room where the details in the second tab could directly be discovered through a direct linkage via the first customer’s name column.

The Objective

To carry out data analysis and a mapping exercise for ~4854 customers.

The Approach

The TresVista team followed the following process:

• Used wildcard functionality of VLOOKUP, where the limited entries with minimal difference in the structure could be mapped
• The Fuzzy Match functionality of the Power Query tool was utilized that mapped entries based on different sensitivities, determining the extent of similarities between two cells
• The cell address was extracted from multiple merges to indicate a direct linkage between the two data sets

The Challenges We Overcame

The major hurdles faced by the TresVista team were:

• Non-Uniform Data: The ‘Customer Name’ tab and the ‘Source’ tab had noted the customer names differently, making the direct mapping using the “Wildcard VLOOKUP” formula impossible. For example, New Okhla Industrial Development Authority had to be mapped against Okhla Developmnt Authorty
• Error Rectification: Due to the usage of different similarity thresholds, there were chances of wrong mapping which needed to be eliminated

The TresVista team overcame these hurdles by using multiple error identification/rectification methods, one of which was the reverse mapping of the entries and then random manual validation.

Final Product (Sanitized)

The Value Add – Catalyzing the Client’s Impact

The TresVista team opted for multiple excel functionality that included Wildcard VLOOKUP, Fuzzy Mapping with multiple levels of sensitivities, and then extracting to link the cell address to have in place a direct linkage between the mapping, making it easy to redirect and discover the other information in the data array.