I have five columns for which I need underlying nested values on a single row. Here is sample data I have:
| ID | Product | Sales Manager | Sales Rep | Rep Status |
|---|---|---|---|---|
| 1 | Product1 | Manager1 | Rep1 | Active |
| 2 | Product1 | Manager1 | Rep2 | Active |
| 3 | Product1 | Manager2 | Rep5 | Active |
| 4 | Product2 | Manager1 | Rep1 | Active |
| 5 | Product2 | Manager2 | Rep5 | Active |
| 6 | Product2 | Manager2 | Rep6 | Active |
| 7 | Product3 | Manager1 | Rep1 | Active |
| 8 | Product3 | Manager1 | Rep4 | Active |
| 9 | Product3 | Manager2 | Rep6 | Active |
I would like to generate consolidated cells consisting of all applicable managers, their reps, and the reps status for each product listed:
...
Product1
Sales Manager: Manager1
Sales Rep: Rep1 (Active)
Sales Rep: Rep2 (Active)
Sales Manager: Manager2
Sales Rep: Rep5 (Active)
Product2
Sales Manager: Manager1
Sales Rep: Rep1 (Active)
Sales Manager: Manager2
Sales Rep: Rep5 (Active)
Sales Rep: Rep6 (Active)
Product3
Sales Manager: Manager1
Sales Rep: Rep1 (Active)
Sales Rep: Rep4 (Active)
Sales Manager: Manager2
Sales Rep: Rep6 (Active)
...
This is the formula I have been using. It can only generate each manager assigned to a product, not the reps assigned to the managers on each product, though:
...
LET(
STAT,$E:$E,
PROD,$B:$B,
MAN,$C:$C,
REP,$D:$D,
IDE,$A:$A,
AL,$A:$E,
IDEA,DROP(FILTER(IDE,NOT(ISBLANK(IDE))),1),
IDS," ID "&IDEA,
STATUS,INDEX(STAT,MATCH(IDEA,IDE,0)),
PRODUCT,INDEX(PROD,MATCH(IDEA,IDE,0)),
PRODUCTS,UNIQUE(PRODUCT),
MANAGE,INDEX(MAN,MATCH(IDEA,IDE,0)),
MANAGES,UNIQUE(MANAGE),
MANAGER,"
Sales Manager:
"&MANAGE,
MANAGERS,UNIQUE(MANAGER),
REPRE,INDEX(REP,MATCH(IDEA,ID,0)),
REPRES,UNIQUE(REPRE),
txxt,BYROW(MANAGES,LAMBDA(a,TEXTJOIN("
",TRUE,UNIQUE(FILTER(REPRE,MANAGE=UNIQUE(a)))))),
REPRESENTATIVE,IF(ISNUMBER(MATCH(REPRE,{"Unspecified"},0)),"","
Sales Representative(s):
"&REPRE),
hoor,VSTACK(txxt),
MANAGING,MANAGERS&REPRESENTATIVE,
txt,BYROW(PRODUCTS,LAMBDA(a,TEXTJOIN("
",TRUE,UNIQUE(FILTER(MANAGER,PRODUCT=UNIQUE(a)))))),
hor,VSTACK(txt),
fin,PROCESSES&"
"&hor,
fin
)
...
I cannot seem to get beyond a list of managers for each product:
...
Product1
Sales Manager: Manager1
Sales Manager: Manager2
Product2
Sales Manager: Manager1
Sales Manager: Manager2
Product3
Sales Manager: Manager1
Sales Manager: Manager2
...
If anyone can help me out in constructing an Excel formula (not VBA Macros or Pivot Tables), then it would be much appreciated.
Thanks!
6 Answers 6
Nested LAMBDAs
BYROW/REDUCE
=LET(data,Sheet1!B2:E10,prod_col,1,man_col,2,rep_col,3,stat_col,4,dlm,CHAR(10),
d,IF(data="","",data),
pc,INDEX(d,,prod_col),
BYROW(UNIQUE(pc),LAMBDA(p,LET(
pf,FILTER(d,pc=p),
mc,INDEX(pf,,man_col),
p&REDUCE("",UNIQUE(mc),LAMBDA(mm,m,LET(
mf,FILTER(pf,mc=m),
mm&dlm&TEXTJOIN(dlm,,"Sales Manager: "&m,
"Sales Rep: "&INDEX(mf,,rep_col)
&" ("&INDEX(mf,,stat_col)&")"))))))))
BYROW/BYROW
=LET(data,Sheet1!B2:E10,prod_col,1,man_col,2,rep_col,3,stat_col,4,dlm,CHAR(10),
d,IF(data="","",data),
pc,INDEX(d,,prod_col),
BYROW(UNIQUE(pc),LAMBDA(p,LET(
pf,FILTER(d,pc=p),
mc,INDEX(pf,,man_col),
TEXTJOIN(dlm,,p,BYROW(UNIQUE(mc),LAMBDA(m,LET(
mf,FILTER(pf,mc=m),
TEXTJOIN(dlm,,"Sales Manager: "&m,
"Sales Rep: "&INDEX(mf,,rep_col)
&" ("&INDEX(mf,,stat_col)&")")))))))))
Comments
My formula solution is:
[G2]=LET(prod,B2:B10,man,"Sm: "&C2:C10,val,"Sr: "&D2:D10&" ("&E2:E10&")",DROP(
REDUCE("",UNIQUE(prod),LAMBDA(a,x,VSTACK(a,
REDUCE(x,UNIQUE(FILTER(man,prod=x)),LAMBDA(b,y,VSTACK(b,y,
UNIQUE(FILTER(val,(prod=x)*(man=y))))))
))),
1))
If you need a product by cell:
=LET(prod,B2:B10,man,"Sm: "&C2:C10,val,"Sr: "&D2:D10&" ("&E2:E10&")",
MAP(UNIQUE(prod),LAMBDA(x,TEXTJOIN(CHAR(10),TRUE,
REDUCE(x,UNIQUE(FILTER(man,prod=x)),LAMBDA(b,y,VSTACK(b,y,
UNIQUE(FILTER(val,(prod=x)*(man=y)))))))))
)
Another option:
=LET(a,B2:E10,
i,LAMBDA(x,CHOOSECOLS(a,x)),
BYROW(DROP(REDUCE(0,UNIQUE(i(1)),
LAMBDA(b,c,
IFNA(VSTACK(b,
HSTACK(c,
DROP(REDUCE(0,UNIQUE(FILTER(C1&": "&i(2),i(1)=c)),
LAMBDA(d,e,
HSTACK(d,
e,
TOROW(FILTER(D1&": "&i(3)&" ("&i(4)&")",(i(1)=c)*(i(2)=TEXTAFTER(e,": "))))))),
,1))),
""))),
1),
LAMBDA(r,TEXTJOIN("
",,r))))
8 Comments
i, but the TEXTJOIN trick is something else. No nested LET though. IYO, Is there something wrong with nesting LETs or is their benefit often insignificant (not necessarily related to this case)?"" instead of using TEXTJOIN(CHAR(10),,r) what do you mean by nested LET?Another efficient way:
=LET(p,PIVOTBY(B2:B11,C1&": "&C2:C11,D1&": "&D2:D11&" ("&E2:E11&")",ARRAYTOTEXT,,0,,0),
q,DROP(p,1),
BYROW(IF(q>"",TAKE(p,1)&"
"&SUBSTITUTE(q,", ","
"),""),LAMBDA(x,TEXTJOIN("
",,x))))
Comments
Pivot table: enter image description here Add a filter if necessary.
Comments
For ease of use convert the data to table
GROUPBY function - Microsoft Support, aggregating
statuswithLAMBDA(x, ARRAYTOTEXT(UNIQUE(x)))for totals
-3: Grand and Subtotals at Top for 3 columnsDROPthe first row with grand total
- Then formatting the grouped result based on the column values for product, manager etc.
=LET(
grouped, DROP(
GROUPBY(
Table1[[Product]:[Sales Rep]],
Table1[Rep Status],
LAMBDA(x, ARRAYTOTEXT(UNIQUE(x))),
0,
-3
),
1
),
sm, LAMBDA(n, "Sales Manager: " & n),
sr, LAMBDA(n, status,
"Sales Rep: " & n & "(" & status & ")"
),
BYROW(
grouped,
LAMBDA(r_,
IFS(
INDEX(r_, 1, 2) = "",
INDEX(r_, 1, 1),
INDEX(r_, 1, 3) = "",
sm(INDEX(r_, 1, 2)),
TRUE,
sr(
INDEX(r_, 1, 3),
INDEX(r_, 1, 4)
)
)
)
)
)
