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!

Can you further elaborate on how to incorporate that into my formula? — RSC001, Aug 22 '23 at 14:20

Scott Craner · Accepted Answer · 2023-08-22T14:25:01.413

2

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

edited Aug 22 '23 at 14:25

answered Aug 22 '23 at 14:19

Scott Craner

148,073
10
49
81

score 2 · Answer 2 · answered Aug 22 '23 at 14:34

2

Only an alternative approach using MAP( ) and assuming Column A can be taken as the column for the last row.

• Formula used in cell E2

=LET(
     lastrow, MATCH(7^89,1/(A:A<>"")),
     x, A2:INDEX(A:B,lastrow,),
     y, TAKE(x,,1),
     z, TAKE(x,,-1),
     w, UNIQUE(z),
     HSTACK(MAP(w,LAMBDA(d,TEXTJOIN(", ",,REPT(y,d=z)))),w))

answered Aug 22 '23 at 14:34

Mayukh Bhattacharya

12,541
5
21
32

I am curious. Why `7^89`? – Ron Rosenfeld Aug 22 '23 at 18:04
Yes, I can use `2` as well or `9^99` as well, when getting the last row number in `MATCH()` function with `1/(A:A<>"")` – Mayukh Bhattacharya Aug 22 '23 at 18:08
1

I would have used `2` as it would be one less operation. – Ron Rosenfeld Aug 22 '23 at 18:15
@RonRosenfeld Sir, I agree with you, but I learnt this trick from @Jos Wooley Sir, so i used in this way, but yes using `2` is also correct and would be less operation, agree with you completely thank you for sharing – Mayukh Bhattacharya Aug 22 '23 at 18:16
1

I use a very large number if I am looking at numeric values that are unknown. And then I'll use something like `9.9E+307`. But with this kind of comparison, the largest number will be `1`. – Ron Rosenfeld Aug 22 '23 at 18:47
@RonRosenfeld Sir, but i got all `1`s so using `2` will be right as you said. shouldnt be? Understood clearly now sir ! – Mayukh Bhattacharya Aug 22 '23 at 18:48
1

Exactly. Using `2` – Ron Rosenfeld Aug 22 '23 at 18:56
@RonRosenfeld great thanks for the guidance ! – Mayukh Bhattacharya Aug 22 '23 at 18:57

Cary Ballard · Answer 3 · 2023-08-23T02:49:22.703

0

A little late, but I think this is a solid formula:

=LET(
    num, D2:D7,
    val, E2:E7,
    uv, UNIQUE(val),
    HSTACK(
        MAP(uv, LAMBDA(m, TEXTJOIN(", ", , FILTER(num, (val = m))))),
        uv
    )
)

edited Aug 23 '23 at 02:49

answered Aug 23 '23 at 02:08

Cary Ballard

111
6

Textjoin with Filter Down a Column

3 Answers3