spilling conditional textjoin

[samdthompson]

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# 

	A	B	C	D	E	F	G	H	I
1	Data	Day1	Day1	Day1	Day2	Day2		Day1	Day2
2	Words	join	this	text	thank	you		join,this,text	thank,you
3	Numbers	5	4	5	5	6		5,4,5	5,6
4	Words	some	more	words	to	join		some,more,words	to,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

[#2]

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

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

Hello, I have attached a copy of the basic problem to the OP. Obviously, the cell references have changed but it shows the nature of what I am trying to achieve.
Cheers

[#8]

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

[#9]

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

[#10 · Accepted solution]

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