Textjoin with Filter Down a Column

Question

Textjoin and filter by values down a column, not in one cell.

I want to join on one row per each unique value in column N on the other sheet. Here is an example of my input data on the other sheet (Sheet2):

Column A (Version), Column N (Value)
1.1  ValueA
1.1  ValueB
1.3  ValueD
1.3  ValueA
1.2  ValueC
1.4  ValueB

I would like this result generated through an automatic filter, not a VBA formula, or copying and pasting per unique value in Column B (Sheet1) / Column N (Sheet2)

Column A, Column B
1.1, 1.3  ValueA
1.1, 1.4  ValueB
1.3       ValueD
1.2       ValueC

I use this formula in A2 and get this result:

=TEXTJOIN(“, “,TRUE,INDEX(‘Sheet2’!$A:$A,MATCH(UNIQUE(FILTER(‘Sheet2’!$N:$N,<‘Sheet2’!$A:$A<>”Column A”)*(‘Sheet2’!$A:$A>0))),‘Sheet2’!$N:$N,0),0)

Column A,                     Column B
1.1, 1.1, 1.3, 1.3, 1.2, 1.4. ValueA
                              ValueB
                              ValueD
                              ValueA
                              ValueC
                              ValueB

I use this formula in A2 and get the correct result but I have to annoyingly copy and paste the formula down the column:

=TEXTJOIN(“, “,TRUE,UNIQUE(FILTER(‘Sheet2’!$A:$A,(((‘Sheet2’!$N:$N=$B2)

Column A,                     Column B
1.1, 1.3                      ValueA
                              ValueB
                              ValueD
                              ValueA
                              ValueC
                              ValueB

I use this formula in A2 and get #N/A value:

=TEXTJOIN(“, “,TRUE,UNIQUE(FILTER(‘Sheet2’!$A:$A,(((‘Sheet2’$N:$N=(UNIQUE(FILTER(‘Sheet2’!$N:$N,(‘Sheet2’!$A:$A<>”Column A”)*(‘Sheet2’!$A:$A>0)))))))))

Does anyone have a formula that can automatically filter the results I would like in the second chart without using VBA or copying and pasting. I want these results to be auto-populated once entered in cell A2.

Thanks in advance!

Answer 1

Use BYROW on the Unique values:

=LET(
    vro,A:A,
    vlo,B:B,
    vr,DROP(FILTER(vro,vro<>""),1),
    vl,DROP(FILTER(vlo,vro<>""),1),
    uq,UNIQUE(vl),
    txt,BYROW(uq,LAMBDA(a,TEXTJOIN(", ",,FILTER(vr,vl=a)))),
    HSTACK(txt,uq))