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.
 
@bearcub I'm more than happy to help and actually grateful I can return the support I have received in this forum.

Please below the updated version with the following changes:

* credit counts now from 1-24 (in this example)
* I removed the limitation of a manager only having 10 reps
* you will see I added 2x fake reps First1 Last1 and First2 Last2 to see if the arrays spill properly
* simply delete those names in G2
There are still ways to make this way better and I assume the areas are:

* I currently split the rep names 2x in variables t and u and then vstack them in the final output. I'm sure this can be done better but I'm not yet that advanced

All the best

H


1738460088951.xlsx
ABCDEFGH
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, First1 Last1, First2 Last2
3T84002677Gregg SwensenNew ACV2Quote Type:New ACVRenew ACV
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
12T84002677 First1 Last1New ACV11
13T84002677 First2 Last2New ACV12
14T84002677Connor lrelandRenew ACV13
15T84002677Gregg SwensenRenew ACV14
16T84002677Jeff MayoRenew ACV15
17T84002677Matt ButlerRenew ACV16
18T84002677 Michael PettinellaRenew ACV17
19T84002677Taylor MannoRenew ACV18
20T84002677Aaron MurphyRenew ACV19
21T84002677Jan BekRenew ACV20
22T84002677Siara MahalRenew ACV21
23T84002677Dmitriy YanovskiRenew ACV22
24T84002677 First1 Last1Renew ACV23
25T84002677 First2 Last2Renew ACV24
Table 1
Cell Formulas
RangeFormula
A1:D25A1=LET( id,G1, t,TEXTSPLIT(G2,,","), u,TEXTSPLIT(G2,,","), new,G3, renew,H3, m,MAKEARRAY(COUNTA(t)*2,1,LAMBDA(a,v,id)), o,MAKEARRAY(COUNTA(t),1,LAMBDA(a,v,new)), p,MAKEARRAY(COUNTA(u),1,LAMBDA(a,v,renew)), VSTACK( HSTACK("Manager-ID","Sales Rep","Quote Type","# of Credit"), HSTACK(m,VSTACK(t,u),VSTACK(o,p),SCAN(0,m,LAMBDA(a,b,a+(b<>""))))) )
Dynamic array formulas.
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Thank you this is great! How could I incorporate your formula to create the table that I added in one of my responses?
 
Upvote 0
@bearcub I think this is where we hit a roadblock in my own learning. What I would suggest is you paste the source data into a sheet as well so that someone can hopefully further assist to finish this off.

I simplified things a bit more so here is latest version

1738460088951.xlsx
ABCDEFGH
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, First1 Last1, First2 Last2
3T84002677Gregg SwensenNew ACV2Quote Type:New ACVRenew ACV
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
12T84002677 First1 Last1New ACV11
13T84002677 First2 Last2New ACV12
14T84002677Connor lrelandRenew ACV13
15T84002677Gregg SwensenRenew ACV14
16T84002677Jeff MayoRenew ACV15
17T84002677Matt ButlerRenew ACV16
18T84002677 Michael PettinellaRenew ACV17
19T84002677Taylor MannoRenew ACV18
20T84002677Aaron MurphyRenew ACV19
21T84002677Jan BekRenew ACV20
22T84002677Siara MahalRenew ACV21
23T84002677Dmitriy YanovskiRenew ACV22
24T84002677 First1 Last1Renew ACV23
25T84002677 First2 Last2Renew ACV24
Table 1
Cell Formulas
RangeFormula
A1:D25A1=LET( id,G1, t,VSTACK(TEXTSPLIT(G2,,","),TEXTSPLIT(G2,,",")), new,G3, renew,H3, m,MAKEARRAY(COUNTA(t),1,LAMBDA(a,v,id)), o,MAKEARRAY(COUNTA(t)/2,1,LAMBDA(a,v,new)), p,MAKEARRAY(COUNTA(t)/2,1,LAMBDA(a,v,renew)), VSTACK( HSTACK("Manager-ID","Sales Rep","Quote Type","# of Credit"), HSTACK(m,t,VSTACK(o,p),SCAN(0,m,LAMBDA(a,b,a+(b<>""))))) )
Dynamic array formulas.
 
Upvote 0
Thank you. You have provided me with more than I was expecting. You have saved us hours of work. I think I have a good starting point ...
 
Upvote 0
Is there a way to have this formula run when the Employee ID has spilled completely?

I have to run this formula for several managers.

I can copy the next ID in the first available row (after the previos manager has spilled) and also copy the array formula so it spills for the next manager

Is there a way to do this dynamically so the formula will apply this formula for all the ID's from a list so I don't manually have to run the formula manually for each nanager?
 
Upvote 0
@bearcub happy to try and help bearing in mind my Timezone (Sydney Aus). There is a tool called xlbb that you can use to copy and paste minisheets. Maybe post a few rows of your source data and we can try to help further.

Cheers H
 
Upvote 0
Thank you for your help. Understood about the time zone, I am in California - 16 hours or more behind you. I have added the Xlbb mini sheets per your request.

The first table is the source data from Sales Operations & the second table is what we need to create from the Sales Ops data to populate tthe manual upload file for the commission program.

The ID number for the second table is associated to Johnny Lam 0 one of the managers.

I have to do the following (2 you have already helped with):

Take all the ID's from in column B (maybe 20-30) and assign them to each of the AE's who report the them so the managers have a Sales Rep assigned to them for new and Renewal business​
Split the names in column N (you have done this for us already)​
Have both a new and Renewal credit assigned to each AE (Sales Rep) so the manager receives both types of credits (you have done this for us already)​
In column J & K, in the second table below, enter the company that AE works for.​


Testing Files for Rep Aligned Crediting Example.xlsx
BCDFGHIJKLMNO
1EE ID #NameBrandRole 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/SA12,1709,6269,53331,329WorldwideBlazeMeter, Perfecto, Static AnalysisWorldwideDan Laun
3T84026777Johnny LamPFT/BLZ5/2/20168,2685,344-13,612AMER, 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/SA7/17/20232,9263,0943,6019,622AMER, Central/West, LATAMBlazeMeter, Perfecto, Static AnalysisBryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David Gordon,Bill Valdez,Joe ValdezPhil Rayment
5RJM78872NPaul FackrellSA3/1/2017--2,5962,596EMEAStatic AnalysisGisella Albanesi,Jurgen Triep,Paul FackrellPhil Rayment
6RJM83711NJulius MongPFT/BLZ6/3/201911,9619,449-21,410WorldwideBlazeMeter, PerfectoWorldwidePhil Rayment
7RJM673523Jamie TorokSA10/21/2024--9,5339,533WorldwideStatic AnalysisWorldwidePhil Rayment
8RJM673120Bharath R VantariPFT/BLZ10/30/202111,3167,683-18,999AMER (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/BLZ10/30/202110,0387,264-17,302AMER (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/BLZ10/30/202110,0387,264-17,302AMER (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/BLZ11/4/20214,4313,769-8,200AMER (West), APACBlazeMeter, PerfectoBlazeMeter, PerfectoRobert Healey,Bryan Hare,Christina Chia,Dustin Hansen,Kyle Buche,Robert Ellerman,David GordonJulius Mong
12RJM673119John BoebingerPFT/BLZ10/30/202110,0387,264-17,302AMER (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/BLZ7/11/201610,0387,264-17,302AMER (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/BLZ3/1/202110,0387,264-17,302AMER (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/BLZ7/11/202210,0387,264-17,302AMER (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/BLZ11/4/202110,0387,264-17,302AMER (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/BLZ10/30/20213,3022,287-5,589EMEA, APACBlazeMeter, PerfectoBlazeMeter, PerfectoRobert Healey,Jan Bek,Siara Mahal,Dmitriy Yanovskiy,TBHJulius Mong
18RJM60280NNato VasilevskiPFT/BLZ2/19/20182,0241,868-3,892EMEA BlazeMeter, PerfectoBlazeMeter, PerfectoJan Bek,Siara Mahal,Dmitriy Yanovskiy,TBHJulius Mong
19RJM46478NSreevatsa SreerangarajuPFT/BLZ10/19/201511,3167,683-18,999AMER (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/BLZ10,0387,264-17,302AMER (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 SaladinoSA4/28/2010--6,5666,566AMER, APAC, excludes India, BangladeshStatic AnalysisStatic AnalysisDavid Gordon, Joe Valdez, Bill Valdez, Darren RileyJamie Torok
22RJM010214Joseph WrijilSA7/1/2018--6,5666,566AMER, APAC, excludes India, BangladeshStatic AnalysisStatic AnalysisDavid Gordon, Joe Valdez, Bill Valdez, Darren RileyJamie Torok
23RJM10630NMichael BaronSA11/25/2019--6,2776,277EMEA, APACStatic AnalysisStatic AnalysisPaul Fackrell, Gisella Albanesi, Jurgen Triep, Darren Riley, Sanjay Shanbhag, Jayaprakash GangannaJamie Torok
24RJM01377NPierre-Edouard LepereSA6/12/2017--6,2776,277EMEA, APACStatic AnalysisStatic AnalysisPaul Fackrell, Gisella Albanesi, Jurgen Triep, Darren Riley, Sanjay Shanbhag, Jayaprakash GangannaJamie Torok
25RJM34219NSanju ColumbusSA10/1/2024--3,6293,629APACStatic AnalysisStatic AnalysisDarren Riley, Sanjay Shanbhag, Jayaprakash GangannaJamie Torok
Final Quota_Hardcode
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C5Cell ValueduplicatestextNO
C6:C7Cell ValueduplicatestextNO
C2:C4Cell ValueduplicatestextNO
C21:C25,C8:C19Cell ValueduplicatestextNO



This is the output sheet that needs to be uploaded into the commission application.
My excercise it to automate this as much as possible because everything is done manually.

Testing Files for Rep Aligned Crediting Example.xlsx
ABCDEFGHIJK
1CaptivateIQ Internal IDEmployee Id IDEmployee Id First Name (Read Only)Employee Id Last Name (Read Only)Quota ValueRow ValueStartEndAEBusiness Line Group for Worldwide NewBusiness Line Group for Worldwide Renewals
2T84026777New ACV11/1/2512/31/25Connor IrelandPERFECTO
3T84026777New ACV21/1/2512/31/25Gregg SwensenPERFECTO
4T84026777New ACV31/1/2512/31/25Jeff MayoPERFECTO
5T84026777New ACV41/1/2512/31/25Matt ButlerPERFECTO
6T84026777New ACV51/1/2512/31/25Michael PettinellaPERFECTO
7T84026777New ACV61/1/2512/31/25Taylor ManoPERFECTO
8T84026777New ACV71/1/2512/31/25Aaron MurphyPERFECTO
9T84026777New ACV81/1/2512/31/25Jan BekPERFECTO
10T84026777New ACV91/1/2512/31/25Siara MahalPERFECTO
11T84026777New ACV101/1/2512/31/25Dmitriy YanovskyPERFECTO
12T84026777New ACV111/1/2512/31/25Connor IrelandBLAZEMETER
13T84026777New ACV121/1/2512/31/25Gregg SwensenBLAZEMETER
14T84026777New ACV131/1/2512/31/25Jeff MayoBLAZEMETER
15T84026777New ACV141/1/2512/31/25Matt ButlerBLAZEMETER
16T84026777New ACV151/1/2512/31/25Michael PettinellaBLAZEMETER
17T84026777New ACV161/1/2512/31/25Taylor ManoBLAZEMETER
18T84026777New ACV171/1/2512/31/25Aaron MurphyBLAZEMETER
19T84026777New ACV181/1/2512/31/25Jan BekBLAZEMETER
20T84026777New ACV191/1/2512/31/25Siara MahalBLAZEMETER
21T84026777New ACV201/1/2512/31/25Dmitriy YanovskyBLAZEMETER
22T84026777Renew ACV211/1/2512/31/25Connor IrelandPERFECTO
23T84026777Renew ACV221/1/2512/31/25Gregg SwensenPERFECTO
24T84026777Renew ACV231/1/2512/31/25Jeff MayoPERFECTO
25T84026777Renew ACV241/1/2512/31/25Matt ButlerPERFECTO
26T84026777Renew ACV251/1/2512/31/25Michael PettinellaPERFECTO
27T84026777Renew ACV261/1/2512/31/25Taylor ManoPERFECTO
28T84026777Renew ACV271/1/2512/31/25Aaron MurphyPERFECTO
29T84026777Renew ACV281/1/2512/31/25Jan BekPERFECTO
30T84026777Renew ACV291/1/2512/31/25Siara MahalPERFECTO
31T84026777Renew ACV301/1/2512/31/25Dmitriy YanovskyPERFECTO
32T84026777Renew ACV311/1/2512/31/25Connor IrelandBLAZEMETER
33T84026777Renew ACV321/1/2512/31/25Gregg SwensenBLAZEMETER
34T84026777Renew ACV331/1/2512/31/25Jeff MayoBLAZEMETER
35T84026777Renew ACV341/1/2512/31/25Matt ButlerBLAZEMETER
36T84026777Renew ACV351/1/2512/31/25Michael PettinellaBLAZEMETER
37T84026777Renew ACV361/1/2512/31/25Taylor ManoBLAZEMETER
38T84026777Renew ACV371/1/2512/31/25Aaron MurphyBLAZEMETER
39T84026777Renew ACV381/1/2512/31/25Jan BekBLAZEMETER
40T84026777Renew ACV391/1/2512/31/25Siara MahalBLAZEMETER
41T84026777Renew ACV401/1/2512/31/25Dmitriy YanovskyBLAZEMETER
42RJM673412New ACV11/1/202512/31/2025Bryan HarePERFECTO
43RJM673412New ACV21/1/202512/31/2025Christina ChiaPERFECTO
44RJM673412New ACV31/1/202512/31/2025Dustin HansenPERFECTO
45RJM673412New ACV41/1/202512/31/2025Kyle BuchePERFECTO
46RJM673412New ACV51/1/202512/31/2025Robert EllermanPERFECTO
47RJM673412New ACV61/1/202512/31/2025David GordonPERFECTO
48RJM673412New ACV71/1/202512/31/2025Bill ValdezPERFECTO
49RJM673412New ACV81/1/202512/31/2025Joe ValdezPERFECTO
50RJM673412New ACV91/1/202512/31/2025Bryan HareBLAZEMETER
51RJM673412New ACV101/1/202512/31/2025Christina ChiaBLAZEMETER
52RJM673412New ACV111/1/202512/31/2025Dustin HansenBLAZEMETER
53RJM673412New ACV121/1/202512/31/2025Kyle BucheBLAZEMETER
54RJM673412New ACV131/1/202512/31/2025Robert EllermanBLAZEMETER
55RJM673412New ACV141/1/202512/31/2025David GordonBLAZEMETER
56RJM673412New ACV151/1/202512/31/2025Bill ValdezBLAZEMETER
57RJM673412New ACV161/1/202512/31/2025Joe ValdezBLAZEMETER
58RJM673412Renew ACV171/1/202512/31/2025Bryan HarePERFECTO
59RJM673412Renew ACV181/1/202512/31/2025Christina ChiaPERFECTO
60RJM673412Renew ACV191/1/202512/31/2025Dustin HansenPERFECTO
61RJM673412Renew ACV201/1/202512/31/2025Kyle BuchePERFECTO
62RJM673412Renew ACV211/1/202512/31/2025Robert EllermanPERFECTO
63RJM673412Renew ACV221/1/202512/31/2025David GordonPERFECTO
64RJM673412Renew ACV231/1/202512/31/2025Bill ValdezPERFECTO
65RJM673412Renew ACV241/1/202512/31/2025Joe ValdezPERFECTO
66RJM673412Renew ACV251/1/202512/31/2025Bryan HareBLAZEMETER
67RJM673412Renew ACV261/1/202512/31/2025Christina ChiaBLAZEMETER
68RJM673412Renew ACV271/1/202512/31/2025Dustin HansenBLAZEMETER
69RJM673412Renew ACV281/1/202512/31/2025Kyle BucheBLAZEMETER
70RJM673412Renew ACV291/1/202512/31/2025Robert EllermanBLAZEMETER
71RJM673412Renew ACV301/1/202512/31/2025David GordonBLAZEMETER
Rep Aligned Crediting-Testing


Thank you for what you have provided me thus far, it will save us hours of work!

I will deconstruct your formulas to understand how they work and hopefully learn how to use them. I haven't used Let or Lamba very much yet but I understand they might writing mega formulas much easier.
 
Upvote 0
Hi bearcub,

Based on those screenshots (which is not the best to share data, please see xl2bb add-on for next thread), and a couple hours later, i've come with this solution:

Classeur1
ABCDEF
1EE ID #NameBusiness Line(S) for New ACV (FY25) (Alpha Order)Line(S) for Renew ACV (FY25)Rep AssociationManager
2RJM673498Phil RaymentBlazeMeter, Perfecto, Static AnalysisWorldwideDan Laun
3T84026777Johnny LamBlazeMeter, PerfectoConnor lreland,Gregg Swensen,Jeff Mayo,Matt Butler, Michael Pettinella,Taylor Manno,Aaron Murphy,Jan Bek,Siara Mahal,Dmitriy Yanovski, First1 Last1, First2 Last2 Phil Rayment
4RJM673412Ozzie AzarianBlazeMeter, Perfecto, Static AnalysisBryan Hare,Christina ChiaPhil Rayment
5RJM78872NPaul FackrellStatic AnalysisGisella Albanesi,Jurgen TriepPhil Rayment
6RJM83711NJulius MongBlazeMeter, PerfectoWorldwidePhil Rayment
7RJM673523Jamie TorokStatic AnalysisWorldwidePhil Rayment
source_data


Classeur1
ABCDEFGHIJK
1CaptivatelQ Internal IDEmployee Id IDFirst NameLast NameQuota ValueRowStartEndAEBusiness Line Group for Worldwide NewBusiness Line Group for Worldwide Renewals
2 RJM673412OzzieAzarianNew ACV11/1/2512/31/25Bryan HareBLAZEMETER
3RJM673412OzzieAzarianNew ACV21/1/2512/31/25Christina ChiaBLAZEMETER
4RJM673412OzzieAzarianRenew ACV31/1/2512/31/25Bryan Hare PERFECTO
5RJM673412OzzieAzarianRenew ACV41/1/2512/31/25Christina Chia PERFECTO
6RJM673498PhilRaymentNew ACV11/1/2512/31/25WorldwideBLAZEMETER
7RJM673498PhilRaymentRenew ACV21/1/2512/31/25Worldwide PERFECTO
8RJM673523JamieTorokNew ACV11/1/2512/31/25WorldwideSTATIC ANALYSIS
9RJM673523JamieTorokRenew ACV21/1/2512/31/25WorldwideSTATIC ANALYSIS
10RJM78872NPaulFackrellNew ACV11/1/2512/31/25Gisella AlbanesiSTATIC ANALYSIS
11RJM78872NPaulFackrellNew ACV21/1/2512/31/25Jurgen TriepSTATIC ANALYSIS
12RJM78872NPaulFackrellRenew ACV31/1/2512/31/25Gisella AlbanesiSTATIC ANALYSIS
13RJM78872NPaulFackrellRenew ACV41/1/2512/31/25Jurgen TriepSTATIC ANALYSIS
14RJM83711NJuliusMongNew ACV11/1/2512/31/25WorldwideBLAZEMETER
15RJM83711NJuliusMongRenew ACV21/1/2512/31/25Worldwide PERFECTO
16T84026777JohnnyLamNew ACV11/1/2512/31/25Connor lrelandBLAZEMETER
17T84026777JohnnyLamNew ACV21/1/2512/31/25Gregg SwensenBLAZEMETER
18T84026777JohnnyLamNew ACV31/1/2512/31/25Jeff MayoBLAZEMETER
19T84026777JohnnyLamNew ACV41/1/2512/31/25Matt ButlerBLAZEMETER
20T84026777JohnnyLamNew ACV51/1/2512/31/25 Michael PettinellaBLAZEMETER
21T84026777JohnnyLamNew ACV61/1/2512/31/25Taylor MannoBLAZEMETER
22T84026777JohnnyLamNew ACV71/1/2512/31/25Aaron MurphyBLAZEMETER
23T84026777JohnnyLamNew ACV81/1/2512/31/25Jan BekBLAZEMETER
24T84026777JohnnyLamNew ACV91/1/2512/31/25Siara MahalBLAZEMETER
25T84026777JohnnyLamNew ACV101/1/2512/31/25Dmitriy YanovskiBLAZEMETER
26T84026777JohnnyLamNew ACV111/1/2512/31/25 First1 Last1BLAZEMETER
27T84026777JohnnyLamNew ACV121/1/2512/31/25 First2 Last2 BLAZEMETER
28T84026777JohnnyLamRenew ACV131/1/2512/31/25Connor lreland PERFECTO
29T84026777JohnnyLamRenew ACV141/1/2512/31/25Gregg Swensen PERFECTO
30T84026777JohnnyLamRenew ACV151/1/2512/31/25Jeff Mayo PERFECTO
31T84026777JohnnyLamRenew ACV161/1/2512/31/25Matt Butler PERFECTO
32T84026777JohnnyLamRenew ACV171/1/2512/31/25 Michael Pettinella PERFECTO
33T84026777JohnnyLamRenew ACV181/1/2512/31/25Taylor Manno PERFECTO
34T84026777JohnnyLamRenew ACV191/1/2512/31/25Aaron Murphy PERFECTO
35T84026777JohnnyLamRenew ACV201/1/2512/31/25Jan Bek PERFECTO
36T84026777JohnnyLamRenew ACV211/1/2512/31/25Siara Mahal PERFECTO
37T84026777JohnnyLamRenew ACV221/1/2512/31/25Dmitriy Yanovski PERFECTO
38T84026777JohnnyLamRenew ACV231/1/2512/31/25 First1 Last1 PERFECTO
39T84026777JohnnyLamRenew ACV241/1/2512/31/25 First2 Last2 PERFECTO
data_for_upload
Cell Formulas
RangeFormula
A2:K39A2=LET(listID,source_data!A2:A7, listName,source_data!B2:B7, listNew,source_data!C2:C7, listRenew,source_data!D2:D7, listRepAsso,source_data!E2:E7, createTblFunction,LAMBDA(id,name,new,renewals,listNames, LET(captivateIq, "", fName,CHOOSECOLS(TEXTSPLIT(name," "),1), lName,RIGHT(name,LEN(name)-LEN(fName)-1), quota,{"New ACV";"Renew ACV"}, start,"1/1/"&RIGHT(YEAR(TODAY()),2), end,"12/31/"&RIGHT(YEAR(TODAY()),2), ae,TEXTSPLIT(listNames,,",",TRUE), busiNew,UPPER(CHOOSECOLS(TEXTSPLIT(new,","),1)), busiReNew,IF(renewals=0,IFERROR(UPPER(CHOOSECOLS(TEXTSPLIT(new,","),2)),busiNew),UPPER(CHOOSECOLS(TEXTSPLIT(renewals,","),1))), REDUCE("",quota,LAMBDA(a,q,VSTACK(MAKEARRAY(ROWS(ae)*2,11,LAMBDA(r,c,IFS(c=1,"",c=2,id,c=3,fName,c=4,lName,AND(c=5,ISODD(r)),CHOOSEROWS(quota,1),AND(c=5,ISEVEN(r)),CHOOSEROWS(quota,2),AND(c=6,ISODD(r)),ROUNDUP(r/2,0),AND(c=6,ISEVEN(r)),ROUNDUP(r/2,0)+ROWS(ae),c=7,start,c=8,end,c=9,CHOOSEROWS(ae,ROUNDUP(r/2,0)),AND(c=10,ISEVEN(r)),"",AND(c=10,ISODD(r)),busiNew,AND(c=11,ISODD(r)),"",AND(c=11,ISEVEN(r)),busiReNew)))))))), 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.


This function might seems a bit heavy at first, but the only thing you have to think about is those ranges:
Excel Formula:
listID,source_data!A2:A7,
listName,source_data!B2:B7,
listNew,source_data!C2:C7,
listRenew,source_data!D2:D7,
listRepAsso,source_data!E2:E7,

'listID' refer to the column 'EE ID #'
'listName' refer to the column 'Name'
'listNew' refer to the column 'Business Line(S) for New ACV (FY25) (Alpha Order)'
'listRenew' refer to the column 'Line(S) for Renew ACV (FY25)'
'listRepAsso' refer to the column 'Rep Association'

And I don't think it will happend but if anybody needs it in french, here's the original version:
Excel Formula:
=LET(listID;source_data!A2:A7;
listName;source_data!B2:B7;
listNew;source_data!C2:C7;
listRenew;source_data!D2:D7;
listRepAsso;source_data!E2:E7;
createTblFunction;LAMBDA(id;name;new;renewals;listNames;
LET(captivateIq; "";
fName;CHOISIRCOLS(FRACTIONNER.TEXTE(name;" ");1);
lName;DROITE(name;NBCAR(name)-NBCAR(fName)-1);
quota;{"New ACV";"Renew ACV"};
start;"1/1/"&DROITE(ANNEE(AUJOURDHUI());2);
end;"12/31/"&DROITE(ANNEE(AUJOURDHUI());2);
ae;FRACTIONNER.TEXTE(listNames;;",";VRAI);
busiNew;MAJUSCULE(CHOISIRCOLS(FRACTIONNER.TEXTE(new;",");1));
busiReNew;SI(renewals=0;SIERREUR(MAJUSCULE(CHOISIRCOLS(FRACTIONNER.TEXTE(new;",");2));busiNew);MAJUSCULE(CHOISIRCOLS(FRACTIONNER.TEXTE(renewals;",");1)));
REDUCE("";quota;LAMBDA(a;q;ASSEMB.V(MAKEARRAY(LIGNES(ae)*2;11;LAMBDA(r;c;SI.CONDITIONS(c=1;"";c=2;id;c=3;fName;c=4;lName;ET(c=5;EST.IMPAIR(r));CHOISIRLIGNES(quota;1);ET(c=5;EST.PAIR(r));CHOISIRLIGNES(quota;2);ET(c=6;EST.IMPAIR(r));ARRONDI.SUP(r/2;0);ET(c=6;EST.PAIR(r));ARRONDI.SUP(r/2;0)+LIGNES(ae);c=7;start;c=8;end;c=9;CHOISIRLIGNES(ae;ARRONDI.SUP(r/2;0));ET(c=10;EST.PAIR(r));"";ET(c=10;EST.IMPAIR(r));busiNew;ET(c=11;EST.IMPAIR(r));"";ET(c=11;EST.PAIR(r));busiReNew))))))));
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))

Bests regards,

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