Nested BYROW in Excel?

Question

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!

Answer 1

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)&")")))))))))

Answer 2

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)))))))))
)