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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
This is a great formula but how do I change the named ranges to sheet name in my file to "Final Quota_Hardcode".

Looking at the explanation I can either go into the file and change the ranges to match your named ranges or change the named sheets to the sheet name in the file and the appropriate columns.

Is the source data in your example the sheet name in my file "Final Quota_Hardcode".

Which do you think might be easier?

Can wait to put it into the sheet. Will this formula take care of every manager ID I have in the spreadsheet?
 
Upvote 0
Ignore the last email. All I have to do is change the source data range in your formula to the spread sheet name and range in my file. The formula should work properly, correct?
 
Upvote 0
Ignore the last email. All I have to do is change the source data range in your formula to the spread sheet name and range in my file. The formula should work properly, correct?
Hi bearcub,

I haven't seen your previous post with datas in xl2bb which would have help me a lot 😂, so yess my sheet has a different name, only change the sheet name (part before "!"). If the source data are in a different file, just make sure that both are open while you test the formula otherwise excel wont be able to look in the closed source file.

An easy way is to remove the part related to the range(eg. Sheet1!A2:A5), then click and drag down and excel will write it for you.

Bests regards,

Vincent
 
Upvote 0
Thanks Vincent. As I was looking at your formula I saw what you did and the range you used was as example. I'll map the ranges to the appropriates in my file and tweak as necessary. Thank you again for all your help.

With Best Regards,

Michael
 
Upvote 0
I was able to run the formula and found that most of it worked - I am not complaining. Other than that, it works great and will save us a lot of time.

What the formula didn't do is create a new and renewal for each business line (BlazeMeter, Perfecto, Static Analysis)

Johnny Lam, for example, should have 40 lines but only has 20. His Business units are Blazemeter & Perfecto. He is receiving New credits for Blazemeter and Renewal Credits for Perfecto. Where would I tweak the formula to spill the missing 20 lines.

If you don't have time, we can do it manually. You have provided us with enough data to save us hours of work.

1738604204694.png


1738604227891.png


Thank you again,

With Kind Regards,

Michael
 
Upvote 0
Hi Bearcub,

No problem, I just refactor the whole formula. In my case, using your data, I only get 20 rows beacaus nothing is given in the column "Business Line(s) for Renew ACV (FY25). Do you mean that the column New ACV contain New and renew and column with Renew is useless in your source page?

Here's the new formula:
- English -
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, "",
fName,CHOOSECOLS(TEXTSPLIT(name," "),1),
lName,RIGHT(name,LEN(name)-LEN(fName)-1),
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(fName,nbAe),createRepeatCol(lName,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(fName,nbAe),createRepeatCol(lName,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))


- French -
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; "";
fName;CHOISIRCOLS(FRACTIONNER.TEXTE(name;" ");1);
lName;DROITE(name;NBCAR(name)-NBCAR(fName)-1);
start;"1/1/"&DROITE(ANNEE(AUJOURDHUI());2);
end;"12/31/"&DROITE(ANNEE(AUJOURDHUI());2);
ae;FRACTIONNER.TEXTE(listNames;;",";VRAI);
nbAe;LIGNES(ae);
busiNew;SI(new=0;"";FRACTIONNER.TEXTE(new;;","));
busiReNew;SI(renewals=0;"";FRACTIONNER.TEXTE(renewals;;","));
createRepeatCol;LAMBDA(val;nRow;MAKEARRAY(nRow;1;LAMBDA(r;c;val)));
tblNew;SI(new=0;"";EXCLURE(REDUCE("";busiNew;LAMBDA(a;n;ASSEMB.V(a;ASSEMB.H(createRepeatCol(captivateIq;nbAe);createRepeatCol(id;nbAe);createRepeatCol(fName;nbAe);createRepeatCol(lName;nbAe);createRepeatCol("New ACV";nbAe);createRepeatCol(0;nbAe);createRepeatCol(start;nbAe);createRepeatCol(end;nbAe);ae;createRepeatCol(n;nbAe);createRepeatCol("";nbAe)))));1));
tblReNew;SI(renewals=0;"";EXCLURE(REDUCE("";busiReNew;LAMBDA(a;n;ASSEMB.V(a;ASSEMB.H(createRepeatCol(captivateIq;nbAe);createRepeatCol(id;nbAe);createRepeatCol(fName;nbAe);createRepeatCol(lName;nbAe);createRepeatCol("Renew ACV";nbAe);createRepeatCol(0;nbAe);createRepeatCol(start;nbAe);createRepeatCol(end;nbAe);ae;createRepeatCol("";nbAe);createRepeatCol(n;nbAe)))));1));
outTempTbl;SI(new=0;tblReNew;SI(renewals=0;tblNew;ASSEMB.V(tblNew;tblReNew)));
ASSEMB.H(CHOISIRCOLS(outTempTbl;1;2;3;4;5);SEQUENCE(LIGNES(outTempTbl);1;1;1);CHOISIRCOLS(outTempTbl;7;8;9;10;11))
));
tempTbl;ASSEMB.H(listID;listName;listNew;listRenew;listRepAsso);
outputTbl;EXCLURE(REDUCE(""; SEQUENCE(LIGNES(tempTbl)); LAMBDA(a;s;ASSEMB.V(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);
TRIERPAR(outputTbl;CHOISIRCOLS(outputTbl;2);1;CHOISIRCOLS(outputTbl;5);1;CHOISIRCOLS(outputTbl;6);1))



Here's the data I used as source page:
worstCaseSenarioExcel.xlsx
ABCDEFGHIJKLM
1https://www.mrexcel.com/board/threads/duplicate-a-sales-engineer-to-an-outside-sales-rep.1269373/page-2#postsNameBrandRole Start Date/Term Date2025 NACV Perfecto2025 NACV Blazemeter2025 NACV Static AnalysisCalculatd 2025 Total NACV Quota $KTerritory for ICP (FY25) Geo, Region, Territory, IndustryBusiness Line(S) for New ACV (FY25) (Alpha Order)Business Line(S) for Renew ACV (FY25)Rep AssociationManager
2RJM673498Phil RaymentPFT/BLZ/SA121709626953331329WorldwideBlazeMeter, Perfecto, Static AnalysisWorldwideDan Laun
3T84026777Johnny LamPFT/BLZ2016-05-028268.398268398275343.64261168385013612.0408800821AMER, East, Canada, EMEABlazeMeter, PerfectoConnor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron Murphy,Jan Bek,Siara Mahal,Dmitriy YanovskiyPhil Rayment
4RJM673412Ozzie AzarianPFT/BLZ/SA2023-07-172926.406926406933094.420600858373600.961538461549621.78906572683AMER, Central/West, LATAMBlazeMeter, Perfecto, Static AnalysisBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Bill Valdez,Joe ValdezPhil Rayment
5RJM78872NPaul FackrellSA2017-03-01002596.153846153852596.15384615385EMEAStatic AnalysisGisella Albanesi,Jurgen Triep,Paul FackrellPhil Rayment
6RJM83711NJulius MongPFT/BLZ2019-06-0311960.59449021409.5WorldwideBlazeMeter, PerfectoWorldwidePhil Rayment
7RJM673523Jamie TorokSA2024-10-210095339533WorldwideStatic AnalysisWorldwidePhil Rayment
8RJM673120Bharath R VantariPFT/BLZ2021-10-30113167683018999AMER (East, West), APACBlazeMeter, PerfectoBlazeMeter, PerfectoRobert Healey,Bryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
9RJM673137Ralph JezierskiPFT/BLZ2021-10-30100387264017302AMER (East, West)BlazeMeter, PerfectoBlazeMeter, PerfectoBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
10RJM673109Matthew AndrusPFT/BLZ2021-10-30100387264017302AMER (East, West)BlazeMeter, PerfectoBlazeMeter, PerfectoBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
11RJM77409NRoberto GarciaPFT/BLZ2021-11-044431376908200AMER (West), APACBlazeMeter, PerfectoBlazeMeter, PerfectoRobert Healey,Bryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David GordonJulius Mong
12RJM673119John BoebingerPFT/BLZ2021-10-30100387264017302AMER (East, West)BlazeMeter, PerfectoBlazeMeter, PerfectoBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
13T84015666Matthew CaldwellPFT/BLZ2016-07-11100387264017302AMER (East, West)BlazeMeter, PerfectoBlazeMeter, PerfectoBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
14RJM672958Mina SprengelerPFT/BLZ2021-03-01100387264017302AMER (East, West)BlazeMeter, PerfectoBlazeMeter, PerfectoBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
15RJM673277Sumit AgarwalPFT/BLZ2022-07-11100387264017302AMER (East, West)BlazeMeter, PerfectoBlazeMeter, PerfectoBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
16RJM52230NTiago SoaresPFT/BLZ2021-11-04100387264017302AMER (East, West)BlazeMeter, PerfectoBlazeMeter, PerfectoBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
17RJM14392NSrdjan NalisPFT/BLZ2021-10-303302228705589EMEA, APACBlazeMeter, PerfectoBlazeMeter, PerfectoRobert Healey,Jan Bek,Siara Mahal,Dmitriy Yanovskiy,TBHJulius Mong
18RJM60280NNato VasilevskiPFT/BLZ2018-02-192024186803892EMEABlazeMeter, PerfectoBlazeMeter, PerfectoJan Bek,Siara Mahal,Dmitriy Yanovskiy,TBHJulius Mong
19RJM46478NSreevatsa SreerangarajuPFT/BLZ2015-10-19113167683018999AMER (East, West), APACBlazeMeter, PerfectoBlazeMeter, PerfectoRobert Healey,Bryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
20RJM673541Tyler MullenPFT/BLZ100387264017302AMER (East, West)BlazeMeter, PerfectoBlazeMeter, PerfectoBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Connor Ireland,Gregg Swensen,Jeff Mayo,Matt Butler,Michael Pettinella,Taylor Manno,Aaron MurphyJulius Mong
21RJM063572Gordon SaladinoSA2010-04-280065666566AMER, APAC, excludes India, BangladeshStatic AnalysisStatic AnalysisDavid Gordon, Joe Valdez, Bill Valdez, Darren RileyJamie Torok
22RJM010214Joseph WrijilSA2018-07-010065666566AMER, APAC, excludes India, BangladeshStatic AnalysisStatic AnalysisDavid Gordon, Joe Valdez, Bill Valdez, Darren RileyJamie Torok
23RJM10630NMichael BaronSA2019-11-250062776277EMEA, APACStatic AnalysisStatic AnalysisPaul Fackrell, Gisella Albanesi, Jurgen Triep, Darren Riley, Sanjay Shanbhag, Jayaprakash GangannaJamie Torok
24RJM01377NPierre-Edouard LepereSA2017-06-120062776277EMEA, APACStatic AnalysisStatic AnalysisPaul Fackrell, Gisella Albanesi, Jurgen Triep, Darren Riley, Sanjay Shanbhag, Jayaprakash GangannaJamie Torok
25RJM34219NSanju ColumbusSA2024-10-010036293629APACStatic AnalysisStatic AnalysisDarren Riley, Sanjay Shanbhag, Jayaprakash GangannaJamie Torok
source_data


The sheet with the formula to upload:
worstCaseSenarioExcel.xlsx
ABCDEFGHIJK
1CaptivatelQ Internal IDEmployee Id IDFirst NameLast NameQuota ValueRowStartEndAEBusiness Line Group for Worldwide NewBusiness Line Group for Worldwide Renewals
2 RJM010214JosephWrijilNew ACV11/1/2512/31/25David GordonStatic Analysis
3RJM010214JosephWrijilNew ACV21/1/2512/31/25 Joe ValdezStatic Analysis
4RJM010214JosephWrijilNew ACV31/1/2512/31/25 Bill ValdezStatic Analysis
5RJM010214JosephWrijilNew ACV41/1/2512/31/25 Darren RileyStatic Analysis
6RJM010214JosephWrijilRenew ACV51/1/2512/31/25David GordonStatic Analysis
7RJM010214JosephWrijilRenew ACV61/1/2512/31/25 Joe ValdezStatic Analysis
8RJM010214JosephWrijilRenew ACV71/1/2512/31/25 Bill ValdezStatic Analysis
9RJM010214JosephWrijilRenew ACV81/1/2512/31/25 Darren RileyStatic Analysis
10RJM01377NPierre-EdouardLepereNew ACV11/1/2512/31/25Paul FackrellStatic Analysis
11RJM01377NPierre-EdouardLepereNew ACV21/1/2512/31/25 Gisella AlbanesiStatic Analysis
12RJM01377NPierre-EdouardLepereNew ACV31/1/2512/31/25 Jurgen TriepStatic Analysis
13RJM01377NPierre-EdouardLepereNew ACV41/1/2512/31/25 Darren RileyStatic Analysis
14RJM01377NPierre-EdouardLepereNew ACV51/1/2512/31/25 Sanjay ShanbhagStatic Analysis
15RJM01377NPierre-EdouardLepereNew ACV61/1/2512/31/25 Jayaprakash GangannaStatic Analysis
16RJM01377NPierre-EdouardLepereRenew ACV71/1/2512/31/25Paul FackrellStatic Analysis
17RJM01377NPierre-EdouardLepereRenew ACV81/1/2512/31/25 Gisella AlbanesiStatic Analysis
18RJM01377NPierre-EdouardLepereRenew ACV91/1/2512/31/25 Jurgen TriepStatic Analysis
19RJM01377NPierre-EdouardLepereRenew ACV101/1/2512/31/25 Darren RileyStatic Analysis
20RJM01377NPierre-EdouardLepereRenew ACV111/1/2512/31/25 Sanjay ShanbhagStatic Analysis
21RJM01377NPierre-EdouardLepereRenew ACV121/1/2512/31/25 Jayaprakash GangannaStatic Analysis
22RJM063572GordonSaladinoNew ACV11/1/2512/31/25David GordonStatic Analysis
23RJM063572GordonSaladinoNew ACV21/1/2512/31/25 Joe ValdezStatic Analysis
24RJM063572GordonSaladinoNew ACV31/1/2512/31/25 Bill ValdezStatic Analysis
25RJM063572GordonSaladinoNew ACV41/1/2512/31/25 Darren RileyStatic Analysis
26RJM063572GordonSaladinoRenew ACV51/1/2512/31/25David GordonStatic Analysis
27RJM063572GordonSaladinoRenew ACV61/1/2512/31/25 Joe ValdezStatic Analysis
28RJM063572GordonSaladinoRenew ACV71/1/2512/31/25 Bill ValdezStatic Analysis
29RJM063572GordonSaladinoRenew ACV81/1/2512/31/25 Darren RileyStatic Analysis
30RJM10630NMichaelBaronNew ACV11/1/2512/31/25Paul FackrellStatic Analysis
data_for_upload (2)
Cell Formulas
RangeFormula
A2:K692A2=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, "", fName,CHOOSECOLS(TEXTSPLIT(name," "),1), lName,RIGHT(name,LEN(name)-LEN(fName)-1), 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(fName,nbAe),createRepeatCol(lName,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(fName,nbAe),createRepeatCol(lName,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))
Dynamic array formulas.


I know, my file is named worstCaseSenarioExcel but don't take it personal :ROFLMAO:

Bests regards,

Vincent
 
Upvote 0
No worries, you didn't have all the information.

"Do you mean that the column New ACV contain New and renew and column with Renew is useless in your source page?",
No, it is correct.
There must be a new and renewal credits for each business and Sales Rep which is why Johnny Lam as 40 lines instead of 20.

His team sells Blazemeter and Perfecto so each rep will have New & Renewal for Blazemeter and New & Renewal for Perfecto - a total of 40 lines

I appreciate you patience and help with this. I have a meeting with my boss on Wednesday to go over the sheet and I think she will be totally floored!
 
Upvote 0
Hi bearcub,

If you need to review the formula don't hesitate to ask, also if you mension this forum during your meeting maybe put an emphasis on the 'Dummy data' because bosses get afraid quickly thinking about data leaks.

if new and renew are the same column, here's an alternative formula by setting listRenew with the same range as listNew:

Excel Formula:
=LET(listID,source_data!A2:A25,
listName,source_data!B2:B25,
listNew,source_data!J2:J25,
listRenew,source_data!J2:J25,
listRepAsso,source_data!L2:L25,
...

Bests regards,

Vincent
 
Upvote 0
Sorry for the confusion. Just ran it, I need to leave but will check out when I get back in a little bit.

Looks fantastic for now.

Oh, I found out we don't need the first and last name. What do I remove from the formula. We could delete it manually if necessary.

Hope you don't mind if I spend time asking you how everything works. This is a fantastc formula@
 
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