Hello There,
Seeking expertise assistance on VBA coding for the below. MS Excel file has been enclosed here with.
Problem: The MS Excel file has 3 tabs (Customer Request, Master Customer List and Employee List). Customers are from two locations. Each customer is mapped to 1 employee (i.e. 1:1 mapping). There are 5 employees supporting 1 location and 2 employees supporting another location. When ever customer request is received, it should assign the employee for the customer doing a historic search from the Master Customer List. If it is a new customer, it should assign a employee from the Employee list and update in Master Customer List
Requirement of VBA to perform:
From the Customer Request tab (referring Customer ID), search in the Master Customer List
If the customer is existing, then assign the employee (like a vlook-up to Master Customer List)
If the customer is not in Master Customer List, then it should refer to the location of the customer and then assign the employee to the new customer.
Also update the new customer details in the Master Customer List
The tricky part would be:
If there are 5 new customers from 1 location, it should get equally distributed to all the employees from the list. (e.g. if there are 5 new customers from ABC location, it should assign 1 customer to each employee managing that location. If there are only 3 new customers from ABC location, it should assign to first 3 employees managing that location etc.)
If a new customer has 10 requests (1 request in 1 row then it will have 10 rows) all the requests for this customer will be assigned to 1 employee (1 employee to 1 customer mapping)
Sample data and required output are in enclosed workbook. Requirements are in tabs and sample output is provided from row 25 in Customer Request tab
Thanks in advance!
Seeking expertise assistance on VBA coding for the below. MS Excel file has been enclosed here with.
Problem: The MS Excel file has 3 tabs (Customer Request, Master Customer List and Employee List). Customers are from two locations. Each customer is mapped to 1 employee (i.e. 1:1 mapping). There are 5 employees supporting 1 location and 2 employees supporting another location. When ever customer request is received, it should assign the employee for the customer doing a historic search from the Master Customer List. If it is a new customer, it should assign a employee from the Employee list and update in Master Customer List
Requirement of VBA to perform:
From the Customer Request tab (referring Customer ID), search in the Master Customer List
If the customer is existing, then assign the employee (like a vlook-up to Master Customer List)
If the customer is not in Master Customer List, then it should refer to the location of the customer and then assign the employee to the new customer.
Also update the new customer details in the Master Customer List
The tricky part would be:
If there are 5 new customers from 1 location, it should get equally distributed to all the employees from the list. (e.g. if there are 5 new customers from ABC location, it should assign 1 customer to each employee managing that location. If there are only 3 new customers from ABC location, it should assign to first 3 employees managing that location etc.)
If a new customer has 10 requests (1 request in 1 row then it will have 10 rows) all the requests for this customer will be assigned to 1 employee (1 employee to 1 customer mapping)
Sample data and required output are in enclosed workbook. Requirements are in tabs and sample output is provided from row 25 in Customer Request tab
Thanks in advance!