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

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Jun 15, 2023

samdthompson 

I think I may need to see your sheet because it's not clear about H1 and if it will change?

 

This may be enough for you to take it and run with:

=LET(
    seq, SEQUENCE(ROWS(data)),
    REDUCE(
        {"Day1", "Day2"},
        seq,
        LAMBDA(a, v,
            LET(
                day_1, TEXTJOIN(",", , FILTER(CHOOSEROWS(data, v), B1# = "Day1")),
                day_2, TEXTJOIN(",", , FILTER(CHOOSEROWS(data, v), B1# = "Day2")),
                VSTACK(a, HSTACK(day_1, day_2))
            )
        )
    )
)
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