Duplicate a Sales Engineer to an outside Sales Rep

[bearcub]

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.

 

[Holger]

Hi Holger,

It seems like I love puzzle, programming and excel which help a lot to find solution! If you want to test separately, try with LAMBDA to forward controled variable to your formula ex:

LAMBDA(a,b,a+b)(A1,A2)

Once it work, implement it in your new formula. Also if you want to loop rows, use MAP because BYROW will make you crazy since it rarely does what it was programmed for. Finaly, VSTACK and HSTACK are one way to go to stack table together, but you can also start with the table with the size you want with MAKEARRAY, start from an existant table and make it bigger wit OFFSET, create your own column with multiple rows manualy with brackets {1,2,34} and much more.

I showed here one way to do it, but there are tousands of way to work around. If nothing seems to work, then we still have a chance with VBA.

On this note, I wish you the best in your Excel learning,

Vincent

Thanks for your kind words Vincent. That Lambda formula I picked up on a YouTube video where someone explained in a practical example how to split an array with Byrow, Scan, Map and Reduce. It visualises the differences these function take and shows the result (array, row, column or just one number). Slowly getting the hang of it and applying it. Mostly I deconstruct to see what each step does and than apply it to some testing files….

All the best for now

H