Duplicate a Sales Engineer to an outside Sales Rep

bearcub

Well-known Member
Joined
May 18, 2005
Messages
754
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. 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.

1738460088951.png


Thank you for your help in advance.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi bearcub,

First of all, which version of excel do you have? Since you have 365 among all that list under your name i'll go with it but might not work with previous version.

If you only need to know the number of credit, I would do it like this:
Excel Formula:
=ROWS(TEXTSPLIT(TEXTJOIN(",",TRUE,E2,E12),,",",TRUE))

If you want to see the whole table, I would do something like that:
Classeur1
ABCDE
1Manager IDQuota ValueTextsplit Result# of creditsSales man assigned to manager
2T84026777New ACVConnor Ireland1Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron Murphy,Jan Bek,Siara Mahal,Dmitriy Yanovskiy
3T84026777New ACVGregg Swensen2
4T84026777New ACVJeff Mayo3
5T84026777New ACVMatt Butler4
6T84026777New ACVMichael Pettinella5
7T84026777New ACVTaylor Manno6
8T84026777New ACVAaron Murphy7
9T84026777New ACVJan Bek8
10T84026777New ACVSiara Mahal9
11T84026777New ACVDmitriy Yanovskiy10
12T84026777Renew ACVConnor Ireland11Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron Murphy,Jan Bek,Siara Mahal,Dmitriy Yanovskiy
13T84026777Renew ACVGregg Swensen12
14T84026777Renew ACVJeff Mayo13
15T84026777Renew ACVMatt Butler14
16T84026777Renew ACVMichael Pettinella15
17T84026777Renew ACVTaylor Manno16
18T84026777Renew ACVAaron Murphy17
19T84026777Renew ACVJan Bek18
20T84026777Renew ACVSiara Mahal19
21T84026777Renew ACVDmitriy Yanovskiy20
Feuil2
Cell Formulas
RangeFormula
A2:A21A2=TEXTSPLIT(REPT("T84026777,",ROWS(TEXTSPLIT(TEXTJOIN(",",TRUE,E2,E12),,",",TRUE))),,",",TRUE)
C2:C21C2=TEXTSPLIT(E2,,",",TRUE)
Dynamic array formulas.


If I had a better representation of the table in input and what you want as output with your actual excel version, I could provide a way better solution than both above.

Bests regards,

Vincent
 
Upvote 0
Sorry, the title is incorrect though it is the same concept. We are mapping, in the commission system, what we call "indirect credits" for commission calculations. Direct credits are for the sales rep that closes the order. Indirect credits are for the "overlays" - the people that manager or do technical work to help the rep close the deal (these are the people behind the scenes.. The list I have to map are are both the Sales Managers and the Sales Engineers (i.e. technical support).
 
Upvote 0
Sorry, the title is incorrect though it is the same concept. We are mapping, in the commission system, what we call "indirect credits" for commission calculations. Direct credits are for the sales rep that closes the order. Indirect credits are for the "overlays" - the people that manager or do technical work to help the rep close the deal (these are the people behind the scenes.. The list I have to map are are both the Sales Managers and the Sales Engineers (i.e. technical support).
I'm not quite sure to understand how this impact the previous formula I gave you, could you be more precise on what we need to change?
 
Upvote 0
Please see below but bear in mind, that I'm still in early stages of learning and I'm 💯 that there are ways to make this more efficient

1738460088951.xlsx
ABCDEFG
1Manager-IDSales RepQuote Type# of CreditManager IDT84002677
2T84002677Connor lrelandNew ACV1Sales RepsConnor lreland,Gregg Swensen,Jeff Mayo,Matt Butler, Michael Pettinella,Taylor Manno,Aaron Murphy,Jan Bek,Siara Mahal,Dmitriy Yanovski
3T84002677Gregg SwensenNew ACV2
4T84002677Jeff MayoNew ACV3
5T84002677Matt ButlerNew ACV4
6T84002677 Michael PettinellaNew ACV5
7T84002677Taylor MannoNew ACV6
8T84002677Aaron MurphyNew ACV7
9T84002677Jan BekNew ACV8
10T84002677Siara MahalNew ACV9
11T84002677Dmitriy YanovskiNew ACV10
12T84002677Connor lrelandRenew ACV1
13T84002677Gregg SwensenRenew ACV2
14T84002677Jeff MayoRenew ACV3
15T84002677Matt ButlerRenew ACV4
16T84002677 Michael PettinellaRenew ACV5
17T84002677Taylor MannoRenew ACV6
18T84002677Aaron MurphyRenew ACV7
19T84002677Jan BekRenew ACV8
20T84002677Siara MahalRenew ACV9
21T84002677Dmitriy YanovskiRenew ACV10
Table 1
Cell Formulas
RangeFormula
A1:D21A1=LET( id,G1, new, "New ACV", renew, "Renew ACV", m,MAKEARRAY(20,1,LAMBDA(a,v,id)), o, MAKEARRAY(10,1,LAMBDA(a,v,new)), p, MAKEARRAY(10,1,LAMBDA(a,v,renew)), t,TEXTSPLIT(G2,,","), u,TEXTSPLIT(G2,,","), VSTACK( HSTACK("Manager-ID","Sales Rep","Quote Type","# of Credit"), HSTACK(m,VSTACK(t,u),VSTACK(o,p),VSTACK(SCAN(0,t,LAMBDA(a,b,a+(b<>""))),SCAN(0,u,LAMBDA(a,b,a+(b<>""))))) ) )
Dynamic array formulas.
 
Upvote 0
This is great, thank you.
The first table is the source data (from Sales Ops)

1738467219011.png


The above table is to be transformed into the below data for upload into the commission application:

1738467586983.png


We had a meeting yesterday and my boss wanted to automate the process. I though first of text split to separate the names . Then I thought of PowerQuery.

The task I have is to dynamically populate column E, J & K from the source data. I don't think I need to pull in the actual quota values because this would be done in a separate upload
You have helped with mapping the reps with the overlays.

I do have 365 for work so I do have access to new formulas Microsoft has been releasing.

Thank you for your help in advance.
 
Upvote 0
Holger that is a thing of beauty, I need to change the numbers from 1 to 10 to 1 to 20. The system needs to know how many total credits the manager should receive

I apologize for my instructions. The row count should be the total of both the new and Renewal orders - 2 for each rep.

If a manager has 13 reps - he will have a couple of 26 (13 New and 13 Renewal).

If I have a list of 40 reps that I have to make this table for on 1 sheet, would I just have to use this formula but add the managers ID and the reps that report to him/her?
 
Upvote 0
Thank you very much for the quick turnaround from both of you. This is great. My manager wanted to have a meeting on Monday and will she be blown away by both approaches.
 
Upvote 0
Sorry coulombevin,, I was writing that response just before I received your reply. Thank you for the formula that was very helpful.

As I was reviewing your answer I received response from Holger so my replies might seem out of order- sorry about that.

Both solutions are brilliant. I now have to figure out how to incorporate both reponses in my upload template.

I had no idea that you could create a page now in Excel using array formulas where in the past something like this would have had to be done using VBA.
 
Upvote 0
Thank you both for your responses. I could only mark 1 as a solution so I used the last one though I give a BIG thumbs up for both solutions.
 
Upvote 0

Forum statistics

Threads
1,226,179
Messages
6,189,476
Members
453,549
Latest member
MBenedikt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top