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