need a VBA for TEXTJOIN FILTER

[MMM_84]

Hi,
I'm trying to write a VBA code for the textjoin filter function, but no success. I need unique values from three columns (A, C and D) place into matrix (range D22:E28) based on matching criteria ("Function", "Company" and "Level"). The following function TEXTJOIN(CHAR(10),TRUE, FILTER($A$2:$A$6&CHAR(10)&$C$2:$C$6&CHAR(10)&$D$2:$D$6,$E$2:$E$6&$B$2:$B$6=$C22&E$21,"")) works perfectly, but I'd like to have it automated and appearing in all cells.

Sub Button1 ()
Dim result As String

result = WorksheetFunction.TextJoin(vbNewLine, True, WorksheetFunction.Filter(Range("$A$2:$A$6") & vbNewLine & Range("$C$2:$C$6") & vbNewLine & Range("$D$2:$D$6"), Range("$E$2:$E$6") & Range("$B$2:$B$6") = Range("$C22&E$21"), " "))


Worksheets("result").Cells(5, 22).Value = result
Worksheets("result").Cells(1, 1).Select


End Sub

Thanks for help

 

[Domenic]

Try the following instead...

    Dim result As String

    With ThisWorkbook.Worksheets("Sheet1")
        result = .Evaluate("TEXTJOIN(CHAR(10),TRUE,FILTER($A$2:$A$6&CHAR(10)&$C$2:$C$6&CHAR(10)&$D$2:$D$6,$E$2:$E$6&$B$2:$B$6=$C22&E$21,""""))")
    End With

Change the sheet name accordingly.

Hope this helps!

 

[MMM_84]

Try the following instead...

    Dim result As String

    With ThisWorkbook.Worksheets("Sheet1")
        result = .Evaluate("TEXTJOIN(CHAR(10),TRUE,FILTER($A$2:$A$6&CHAR(10)&$C$2:$C$6&CHAR(10)&$D$2:$D$6,$E$2:$E$6&$B$2:$B$6=$C22&E$21,""""))")
    End With

Change the sheet name accordingly.

Hope this helps!

that works perfectly! Thanks a ton! Would you also advise how to repeat the same formula to other cells (D22,D23 and etc)?

 

[Domenic]

Please post your sample data and outcome as text. Or, better yet, use the xl2BB add-in instead. Data from images can't be copied and pasted into a worksheet for testing.

Thanks!