Forum Discussion

samdthompson's avatar
samdthompson
Copper Contributor
Jun 15, 2023
Solved

spilling conditional textjoin

Hello, I am needing some help to make a spilling conditional textjoin. I can do this in power query no worries but thats not an option in this case, it has to be done by formula and it must spill.  ...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Jun 15, 2023

    samdthompson 

    If you're going to add more days, then maybe MAKEARRAY is the ticket:

     

     

    =LET(
    rows, ROWS(D13#),
    uDays, UNIQUE(D12#, 1),
    cols, COLUMNS(uDays),
    MAKEARRAY(
        rows,
        cols,
        LAMBDA(r, c,
            LET(
                d, INDEX(uDays, , c),
                TEXTJOIN(",", , FILTER(CHOOSEROWS(D13#, r), D12# = d))
            )
        )
    )
)

     

samdthompson's avatar
samdthompson
Copper Contributor
Jun 15, 2023
Hello, I have attached a copy of the basic problem. Obviously, the cell references have changed but it shows the nature of what I am trying to achieve.
Cheers
Patrick2788's avatar
Patrick2788
Silver Contributor
Jun 15, 2023

samdthompson 

With REDUCE:

=LET(
    header, UNIQUE(D12#, 1),
    seq, SEQUENCE(ROWS(D13#)),
    REDUCE(
        header,
        seq,
        LAMBDA(a, v,
            LET(
                Day_1, TEXTJOIN(",", , FILTER(CHOOSEROWS(D13#, v), D12# = "Day1")),
                Day_2, TEXTJOIN(",", , FILTER(CHOOSEROWS(D13#, v), D12# = "Day2")),
                VSTACK(a, HSTACK(Day_1, Day_2))
            )
        )
    )
)
  • samdthompson's avatar
    samdthompson
    Copper Contributor
    Jun 15, 2023
    Thanks for your time on this. The issue is that what happens when a third day comes in or a seventh? I am needing it to spill based on the unique days Day1...Dayn. I can sum etc using MAKEARRAY but the textjoining just seems far harder than is warranted.
    Cheers
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Jun 15, 2023

      samdthompson 

      If you're going to add more days, then maybe MAKEARRAY is the ticket:

       

       

      =LET(
    rows, ROWS(D13#),
    uDays, UNIQUE(D12#, 1),
    cols, COLUMNS(uDays),
    MAKEARRAY(
        rows,
        cols,
        LAMBDA(r, c,
            LET(
                d, INDEX(uDays, , c),
                TEXTJOIN(",", , FILTER(CHOOSEROWS(D13#, r), D12# = d))
            )
        )
    )
)

       

Resources