bearcub
Well-known Member
- Joined
- May 18, 2005
- Messages
- 754
- Office Version
- 365
- 2013
- 2010
- 2007
- Platform
- Windows
Our Sales Ops team is providing us table to Map Sales Managers to their direct reports for commission purposes. They first have to receive sales credits before commissions can be calculated.
To do this, we have to create Sales Hierarchy mapping in our commission application to create a relationship between the Rep and the Manager.
I am given the Manager's ID number and the reps that report to them in one cell - there could be 30 rep names in that cell. On a separate sheet, for the system upload, I have to create list with all the reps name mapped to the Mamagers ID number. I record (row) for the relationship.
I've used the TextSplit function to split the cell with the reps names but how do I get the Sales manager's ID to repeat for each rep name so I have a distinct record for the manager and the rep?
Below is a manager who has 10 reps reporting to him. The Manger is to receive 20 total sales credits (10 for New Business and 10 for Renewal Business - thus # of credits equaling 20).
I've used to the TextSplit to spill all 10 reps into 10 rows for new business and renewals but how would I get the Employee ID T84026777 to dynamically appear 19 more times for all of the manager's "indirect" credits.
I was thinking of using PowerQuery but I don't know how to split the cell into separate row. I can split the cell into columns but how do I split into rows so that the Sales Manager's ID number 20 times.
Thank you for your help in advance.
To do this, we have to create Sales Hierarchy mapping in our commission application to create a relationship between the Rep and the Manager.
I am given the Manager's ID number and the reps that report to them in one cell - there could be 30 rep names in that cell. On a separate sheet, for the system upload, I have to create list with all the reps name mapped to the Mamagers ID number. I record (row) for the relationship.
I've used the TextSplit function to split the cell with the reps names but how do I get the Sales manager's ID to repeat for each rep name so I have a distinct record for the manager and the rep?
Below is a manager who has 10 reps reporting to him. The Manger is to receive 20 total sales credits (10 for New Business and 10 for Renewal Business - thus # of credits equaling 20).
I've used to the TextSplit to spill all 10 reps into 10 rows for new business and renewals but how would I get the Employee ID T84026777 to dynamically appear 19 more times for all of the manager's "indirect" credits.
I was thinking of using PowerQuery but I don't know how to split the cell into separate row. I can split the cell into columns but how do I split into rows so that the Sales Manager's ID number 20 times.
Thank you for your help in advance.