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