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.

 

The basic requirement is I want to concatenate the values in B2# if the value in H1# matches the value in B1# 

 

 ABCDEFGHI 
1DataDay1Day1Day1Day2Day2 Day1Day2 
2Wordsjointhistextthankyou join,this,textthank,you 
3Numbers54556 5,4,55,6 
4Wordssomemorewordstojoin some,more,wordsto,join 

 

My starting point was this:

 

=MAKEARRAY(
    ROWS(B2#),
    COLUMNS(H1#),
    LAMBDA(r,c,
        BYROW(B2#,LAMBDA(a,TEXTJOIN(",",TRUE,IF(B1#=H1#,a))))
   )
)

 

but clearly it doesn't work. Any assistance, much appreciated,
Cheers

 

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))
            )
        )
    )
)
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 15, 2023

    samdthompson 

    =BYROW($B$2:$F$4,LAMBDA(x,TEXTJOIN(",",,FILTER(x,$B$1:$F$1=H1))))

     

    An alternative could be this formula. The formula is in cell H6 in the example and filled to cell I6.

    • samdthompson's avatar
      samdthompson
      Copper Contributor
      Jun 15, 2023
      Hello, thanks, this is almost what I am needing. I need it to Spill based on H1# rather than H1 since the Number of unique Days will change
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Jun 15, 2023

        samdthompson 

        In my understanding the formula in H1# spills arrays of e.g. Day1, Day2 or Day1, Day2, Day3 across range H1:I1 or H1:J1. However even if you shared the formula from cell H1# i probably couldn't suggest a formula that dynamically spills the results for 2 or 3 or more days (and across 2 or 3 or more columns).

         

        A workaround could be with IFERROR.

         

        =IFERROR(BYROW($B$2:$F$4,LAMBDA(x,TEXTJOIN(",",,FILTER(x,$B$1:$F$1=H1)))),"")

         

        This formula is in cell H6 and filled across range H6:Z6.

        Because of IFERROR empty cells are returned in range J6:Z8. If the formula in H1# spills Day1 to Day4 across range H1:K1 and these days are found in range B1:F1 then the results are returned in range H6:K8.

Resources