Duplicate a Sales Engineer to an outside Sales Rep

bearcub

Well-known Member
Joined
May 18, 2005
Messages
757
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.
 
Hi bearcub,

No problem, if you dont want the name change for this:
Excel Formula:
=LET(listID,source_data!A2:A25,
listName,source_data!B2:B25,
listNew,source_data!J2:J25,
listRenew,source_data!K2:K25,
listRepAsso,source_data!L2:L25,
createTblFunction,LAMBDA(id,name,new,renewals,listNames,
LET(captivateIq, "",
start,"1/1/"&RIGHT(YEAR(TODAY()),2),
end,"12/31/"&RIGHT(YEAR(TODAY()),2),
ae,TEXTSPLIT(listNames,,",",TRUE),
nbAe,ROWS(ae),
busiNew,IF(new=0,"",TEXTSPLIT(new,,",")),
busiReNew,IF(renewals=0,"",TEXTSPLIT(renewals,,",")),
createRepeatCol,LAMBDA(val,nRow,MAKEARRAY(nRow,1,LAMBDA(r,c,val))),
tblNew,IF(new=0,"",DROP(REDUCE("",busiNew,LAMBDA(a,n,VSTACK(a,HSTACK(createRepeatCol(captivateIq,nbAe),createRepeatCol(id,nbAe),createRepeatCol("",nbAe),createRepeatCol("",nbAe),createRepeatCol("New ACV",nbAe),createRepeatCol(0,nbAe),createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,createRepeatCol(n,nbAe),createRepeatCol("",nbAe))))),1)),
tblReNew,IF(renewals=0,"",DROP(REDUCE("",busiReNew,LAMBDA(a,n,VSTACK(a,HSTACK(createRepeatCol(captivateIq,nbAe),createRepeatCol(id,nbAe),createRepeatCol("",nbAe),createRepeatCol("",nbAe),createRepeatCol("Renew ACV",nbAe),createRepeatCol(0,nbAe),createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,createRepeatCol("",nbAe),createRepeatCol(n,nbAe))))),1)),
outTempTbl,IF(new=0,tblReNew,IF(renewals=0,tblNew,VSTACK(tblNew,tblReNew))),
HSTACK(CHOOSECOLS(outTempTbl,1,2,3,4,5),SEQUENCE(ROWS(outTempTbl),1,1,1),CHOOSECOLS(outTempTbl,7,8,9,10,11))
)),
tempTbl,HSTACK(listID,listName,listNew,listRenew,listRepAsso),
outputTbl,DROP(REDUCE("", SEQUENCE(ROWS(tempTbl)), LAMBDA(a,s,VSTACK(a,createTblFunction(INDEX(tempTbl,MAX(s),1),INDEX(tempTbl,MAX(s),2),INDEX(tempTbl,MAX(s),3),INDEX(tempTbl,MAX(s),4),INDEX(tempTbl,MAX(s),5))))),1),
SORTBY(outputTbl,CHOOSECOLS(outputTbl,2),1,CHOOSECOLS(outputTbl,5),1,CHOOSECOLS(outputTbl,6),1))

It is not the best way to do it, I only removed fName and lName. Once well find the final version of the formula, I will refactor the formula to prevent unecessary memory usage like name manipulation.

Bests regards,

Vincent
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
This is a good version. I copied your formula and compared against our records and they match 100%. The next step for me in the coming days is to understand what you did. A lot of the formula I understand, though I couldn't begin to create it.

How did you start writing the formula? I started with the text split for the reps but had no idea that you could recreate the entire page with one formula. I would have thought you could do this with PowerQuery or VBA only.
 
Upvote 0

Hi bearcub,

Since you're good to go with the formula, Here's aversion without the employee first and last name (less range, less memory required):

Excel Formula:
=LET(listID,source_data!A2:A25,
listNew,source_data!J2:J25,
listRenew,source_data!K2:K25,
listRepAsso,source_data!L2:L25,
createTblFunction,LAMBDA(id,new,renewals,listNames,
LET(start,"1/1/"&RIGHT(YEAR(TODAY()),2),
end,"12/31/"&RIGHT(YEAR(TODAY()),2),
ae,TEXTSPLIT(listNames,,",",TRUE),
nbAe,ROWS(ae),
busiNew,IF(new=0,"",TEXTSPLIT(new,,",")),
busiReNew,IF(renewals=0,"",TEXTSPLIT(renewals,,",")),
createRepeatCol,LAMBDA(val,nRow,MAKEARRAY(nRow,1,LAMBDA(r,c,val))),
emptyCol,createRepeatCol("",nbAe),
tblNew,IF(new=0,"",DROP(REDUCE("",busiNew,LAMBDA(a,n,VSTACK(a,HSTACK(emptyCol,createRepeatCol(id,nbAe),emptyCol,emptyCol,createRepeatCol("New ACV",nbAe),emptyCol,createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,createRepeatCol(n,nbAe),emptyCol)))),1)),
tblReNew,IF(renewals=0,"",DROP(REDUCE("",busiReNew,LAMBDA(a,n,VSTACK(a,HSTACK(emptyCol,createRepeatCol(id,nbAe),emptyCol,emptyCol,createRepeatCol("Renew ACV",nbAe),emptyCol,createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,emptyCol,createRepeatCol(n,nbAe))))),1)),
outTempTbl,IF(new=0,tblReNew,IF(renewals=0,tblNew,VSTACK(tblNew,tblReNew))),
HSTACK(CHOOSECOLS(outTempTbl,1,2,3,4,5),SEQUENCE(ROWS(outTempTbl),1,1,1),CHOOSECOLS(outTempTbl,7,8,9,10,11))
)),
tempTbl,HSTACK(listID,listNew,listRenew,listRepAsso),
outputTbl,DROP(REDUCE("", SEQUENCE(ROWS(tempTbl)), LAMBDA(a,s,VSTACK(a,createTblFunction(INDEX(tempTbl,MAX(s),1),INDEX(tempTbl,MAX(s),2),INDEX(tempTbl,MAX(s),3),INDEX(tempTbl,MAX(s),4))))),1),
SORTBY(outputTbl,CHOOSECOLS(outputTbl,2),1,CHOOSECOLS(outputTbl,5),1,CHOOSECOLS(outputTbl,6),1))

How did I came up with that
You have to see it as a puzzle and need to put the pieces together, but first you have to define the pieces! also, if you repeat something, formulate what you do and then find a way to tell your pc how to do it. Finaly, I showed you one way to do it, but there is probably a tousand way to do so.

What do I repeat manually:
  • Separate names on each comma
  • copy the first name of the business for each of these names
  • repeat empty column, employee id, empty column, empty column, quota value, make a sequence of number for "row", repeat start, end, put all the names and finaly repeat business name
  • i do the previous step for all new business
  • i duplicate the output and change quota value to renewal and take the business new to renewal
  • i redo all those steps for each emplyee in the sheet
There is a lot to do here but when we decompose every thing in tiny function, with end up with the whole formula.

TIPS: if you do everything at once, you'll probably die of confusion, do baby steps, ex: split names, create a table with all the info with one business, then take that formula and try to loop all business, then try to merge all those table, then try to loop the whole source data and again merge the result of each loop.

How does it work ... Sorcery?

Starting with the function createTblFunction:
to create the table for one employe i need, and id, new business, renewals business and the list of AE, so i create a function (lambda) with those argument:​
LAMBDA(id,new,renewals,listNames,
now that the user will provide me the informations, I need to create the start, end, ae list, new business list and renewals business list using LET function wich give you the opportunity to create variables and giving value to them:​

Start is = to 1/1/two last number of the current year and end is the same but with 12/31/​
start: "1/1/"&RIGHT(YEAR(TODAY()),2)
end: "12/31/"&RIGHT(YEAR(TODAY()),2)
ae is listNames but splitted at each comma, just like new and renewals business. Also we will need the number of rows so why not get it right now by conting the number of lines of ae table​
ae: TEXTSPLIT(listNames,,",",TRUE)
nbAe: ROWS(ae)
busiNew: IF(new=0,"",TEXTSPLIT(new,,","))
busiReNew: IF(renewals=0,"",TEXTSPLIT(renewals,,","))
we repeat a lot of things (empty, id, start, end, quota value, business name) so lets create a function that will produce a column filled with the wanted value the number of time (row) with want​
createRepeatCol: LAMBDA(val,nRow,MAKEARRAY(nRow,1,LAMBDA(r,c,val)))
since empty column are frequent, lets already set an empty column a an object insted of caling the function multiple time​
emptyCol: createRepeatCol("",nbAe)
Now that we have everything we need, lets create the first table of new element. For the first time, we want to stack multiple column together, empty, id, empty, empty, quota value, row unknown for now so lets say empty, start, end, ae, new, empty. If it is not empty and is a repeated element, lets use our new function 'createRepeatCol'​
HSTACK(emptyCol,createRepeatCol(id,nbAe),emptyCol,emptyCol,createRepeatCol("New ACV",nbAe),emptyCol,createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,createRepeatCol(n,nbAe),emptyCol)
We have our first part of the table, we have to repeat it for all new business and stack them one on top of another. I use reduce beacause byRow and byCol are excel function with a lot of error. With reduce, we have our start table (nothing) and we want to do something for each new business. for each element in new business, lambda will do what we ask then the output of it will be stack (VSTACK) under the start table. That result will then replace our old start table and lambda will do what we ask again, with the second new business and stack the result under the table we already had. (purple is previous step from now on)​
REDUCE("",busiNew,LAMBDA(a,n,VSTACK(a,HSTACK(emptyCol,createRepeatCol(id,nbAe),emptyCol,emptyCol,createRepeatCol("New ACV",nbAe),emptyCol,createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,createRepeatCol(n,nbAe),emptyCol))))
Our table now contain all the repeated datas for every new business. In the previous step, we started with nothing ("") so our first row is all empty we must remove it (DROP) and what if there are no new business given (new = 0)? our table should return nothing. Finaly lets keep all this in a variable tblNew​
tblNew: IF(new=0,"",DROP(REDUCE("",busiNew,LAMBDA(a,n,VSTACK(a,HSTACK(emptyCol,createRepeatCol(id,nbAe),emptyCol,emptyCol,createRepeatCol("New ACV",nbAe),emptyCol,createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,createRepeatCol(n,nbAe),emptyCol)))),1))
For our renewals we will proceed with the same thing but insted of using new data, we will use renewals and invert the last column from new to renew​
tblReNew: IF(renewals=0,"",DROP(REDUCE("",busiReNew,LAMBDA(a,n,VSTACK(a,HSTACK(emptyCol,createRepeatCol(id,nbAe),emptyCol,emptyCol,createRepeatCol("Renew ACV",nbAe),emptyCol,createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,emptyCol,createRepeatCol(n,nbAe))))),1))
We now need to stack new and renewals tables, only if both business have been given by the user:​
outTempTbl: IF(new=0,tblReNew,IF(renewals=0,tblNew,VSTACK(tblNew,tblReNew)))
The only thing left for this employe is to give im his points (row), so lets split the output table, change row column by a sequence from 1 to the number of rows and stick back all the column together (HSTACK):​
HSTACK(CHOOSECOLS(outTempTbl,1,2,3,4,5),SEQUENCE(ROWS(outTempTbl),1,1,1),CHOOSECOLS(outTempTbl,7,8,9,10,11))

Amazing, now we have a function that create all our formated data for one employee, but we have multiple ones lets start by knowing where can we find those data? we need id, new and renewal business and rep asso names. Lets put that in a simple temporary table:
listID: source_data!A2:A25
listNew: source_data!J2:J25
listRenew: source_data!K2:K25
listRepAsso: source_data!L2:L25
tempTbl: HSTACK(listID,listNew,listRenew,listRepAsso)


Again, to prevent bugs from excel by row, lets loop all our temporary table using reduce and we will loop by index (AKA row number). For each row, I want to call createTblFunction with the data of the actual rows and then I want to stack the result of each row to get a giant table.
REDUCE("", SEQUENCE(ROWS(tempTbl)), LAMBDA(a,s,VSTACK(a,createTblFunction(INDEX(tempTbl,MAX(s),1),INDEX(tempTbl,MAX(s),2),INDEX(tempTbl,MAX(s),3),INDEX(tempTbl,MAX(s),4)))))

Dont forget, we started with nothing, so our firts line will be empty, we need to remove it to! and lets put it in a variable named outputTbl
outputTbl: DROP(REDUCE("", SEQUENCE(ROWS(tempTbl)), LAMBDA(a,s, VSTACK(a,createTblFunction(INDEX(tempTbl,MAX(s),1),INDEX(tempTbl,MAX(s),2),INDEX(tempTbl,MAX(s),3),INDEX(tempTbl,MAX(s),4))))),1)

Finaly, lets output all that beauty but not without sorting it based on id then on quota value then on row number!
SORTBY(outputTbl,CHOOSECOLS(outputTbl,2),1,CHOOSECOLS(outputTbl,5),1,CHOOSECOLS(outputTbl,6),1))


Hope this will help you a bit,

Vincent
 
Upvote 0
Yes, it does. thank you for the lengthy explanation. Yes, it is like a puzzle and you have to put the pieces together.

I went through this when I was learning to orchestrate music that I had written for piano and guitar. Very complex!

Oh, if we didn't want the ID numbers to be sorted but wanted them to appear in the order that they appeared on the worksheet, would I remove the sortby line?
 
Upvote 0
yes indeed, remove the sortby line and only put 'outputTbl' like so: ***Please note that I added here a TRIM function for name, business new or renewals because some of them start or end with a space which could cause unwanted issure (it remove that space in front or at the end or double spaces)***

Excel Formula:
=LET(listID,source_data!A2:A25,
listNew,source_data!J2:J25,
listRenew,source_data!K2:K25,
listRepAsso,source_data!L2:L25,
createTblFunction,LAMBDA(id,new,renewals,listNames,
LET(start,"1/1/"&RIGHT(YEAR(TODAY()),2),
end,"12/31/"&RIGHT(YEAR(TODAY()),2),
ae,TRIM(TEXTSPLIT(listNames,,",",TRUE)),
nbAe,ROWS(ae),
busiNew,IF(new=0,"",TEXTSPLIT(new,,",")),
busiReNew,IF(renewals=0,"",TEXTSPLIT(renewals,,",")),
createRepeatCol,LAMBDA(val,nRow,MAKEARRAY(nRow,1,LAMBDA(r,c,val))),
emptyCol,createRepeatCol("",nbAe),
tblNew,IF(new=0,"",DROP(REDUCE("",busiNew,LAMBDA(a,n,VSTACK(a,HSTACK(emptyCol,createRepeatCol(id,nbAe),emptyCol,emptyCol,createRepeatCol("New ACV",nbAe),emptyCol,createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,createRepeatCol(TRIM(n),nbAe),emptyCol)))),1)),
tblReNew,IF(renewals=0,"",DROP(REDUCE("",busiReNew,LAMBDA(a,n,VSTACK(a,HSTACK(emptyCol,createRepeatCol(id,nbAe),emptyCol,emptyCol,createRepeatCol("Renew ACV",nbAe),emptyCol,createRepeatCol(start,nbAe),createRepeatCol(end,nbAe),ae,emptyCol,createRepeatCol(TRIM(n),nbAe))))),1)),
outTempTbl,IF(new=0,tblReNew,IF(renewals=0,tblNew,VSTACK(tblNew,tblReNew))),
HSTACK(CHOOSECOLS(outTempTbl,1,2,3,4,5),SEQUENCE(ROWS(outTempTbl),1,1,1),CHOOSECOLS(outTempTbl,7,8,9,10,11))
)),
tempTbl,HSTACK(listID,listNew,listRenew,listRepAsso),
outputTbl,DROP(REDUCE("", SEQUENCE(ROWS(tempTbl)), LAMBDA(a,s,VSTACK(a,createTblFunction(INDEX(tempTbl,MAX(s),1),INDEX(tempTbl,MAX(s),2),INDEX(tempTbl,MAX(s),3),INDEX(tempTbl,MAX(s),4))))),1),
outputTbl)

bests regards,

Vincent
 
Upvote 0
Thank you for much for your feed back. I will let you know the feedback I get tomorrow when I meet with the team.
 
Upvote 0
Dude @coulombevin what are you having for breakfast.... Thank You so much for the opportunity to learn from this example. Whenever time permits I started deconstructing the formula and get regularly stuck. I try to put each component into a column next to each other to see what each step does. I understand not all parts can be separately tested so this will help tremendously. What I'm struggling most is to loop through rows and extend the number of rows from the initial number that the ae array created. This will help and hopefully, I get there.

Cheers again.
 
Upvote 0
Hi Vincent,

Thank for the explanation. I will play with it when I get a chance to understand how it works.

I just had my meeting with my manager and she was impressed. We are going to receiving other files shortly so we will hopefully have the same results as this one.

Would it be alright if I ping you in this thread in the future if we run into any difficulties?
 
Upvote 0
Dude @coulombevin what are you having for breakfast.... Thank You so much for the opportunity to learn from this example. Whenever time permits I started deconstructing the formula and get regularly stuck. I try to put each component into a column next to each other to see what each step does. I understand not all parts can be separately tested so this will help tremendously. What I'm struggling most is to loop through rows and extend the number of rows from the initial number that the ae array created. This will help and hopefully, I get there.

Cheers again.
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:

Excel Formula:
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
 
Upvote 0
Hi bearcub,

No problem at all, i'll be there if you need me.

See you around,

Vincent
 
Upvote 0

Forum statistics

Threads
1,226,329
Messages
6,190,321
Members
453,604
Latest member
ADJ2RGJ

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