Textjoin and Filter using VBA

[BritsBlitz]

I have the following Array and I'm trying to use TextJoin and Filter in VBA to combine the elements of the array and place it in one cell

Farmer John	Apples	Oranges	Peaches
Farmer Mike	Pears	Bananas	Kiwi
Farmer John	Grapes	Lemons	Strawberries
Farmer Steve	Guavas	Nectarines	Dragon Fruit

For instance, for Farmer John, the output should be Apples, Oranges, Peaches, Grapes, Lemons, Strawberries

I'm using a FOR loop to step through each of the array entries and TextJoin to combine the text for each array line together, but would like to combine the text from multiple array lines together if they match.

Hardcoding it I did the following:

For ArrayStart = 1 to 4
    If FarmerArray(ArrayStart, 1) = "Farmer John" Then
        Worksheets("Farmers").Cells(1,1).Value = Worksheetfunction.TextJoin(", ", True, FarmerArray(ArrayStart,2), FarmerArray(ArrayStart,3), FarmerArray(ArrayStart,4))
    End If
Next ArrayStart

This code will produce "Apples, Orange, Peaches" but when the next condition of Farmer John = True comes around, it overwrites it with "Grapes, Lemons, Strawberries" How do I add it all together instead of overwriting it? Using a formula, I can use the FILTER function, but not sure how to apply that in VBA.

 

[ExcelToDAX]

I am not sure what your FarmerArray is doing, can you paste more of the code?

Without VBA, here is how you would accomplish it:

=TEXTJOIN(",",TRUE,FILTER(B1:D4,A1:A4="Farmer John"))

But you are obviously asking for a VBA solution but I am not sure when your FarmerArray is doing

 

[BritsBlitz]

I am not sure what your FarmerArray is doing, can you paste more of the code?

Without VBA, here is how you would accomplish it:

=TEXTJOIN(",",TRUE,FILTER(B1:D4,A1:A4="Farmer John"))

But you are obviously asking for a VBA solution but I am not sure when your FarmerArray is doing

Hi, I already have the Excel formula working as you wrote it above. I started with the formula and then decided to do it in VBA so my question is how do I rewrite the Excel formula into an equivalent VBA code? The FarmerArray is just an array I created from a much larger table to narrow down the results instead of filtering through the entire table that could be thousands of lines.

 

[Akuini]

Try this:

Dim tx As String
For ArrayStart = 1 To 4
    If FarmerArray(ArrayStart, 1) = "Farmer John" Then
        tx = tx & ", " & WorksheetFunction.TextJoin(", ", True, FarmerArray(ArrayStart, 2), FarmerArray(ArrayStart, 3), FarmerArray(ArrayStart, 4))
    End If
Next ArrayStart
Worksheets("Farmers").Cells(1, 1) = Mid(tx, 3)
End With

 

[BritsBlitz]

Try this:

Dim tx As String
For ArrayStart = 1 To 4
    If FarmerArray(ArrayStart, 1) = "Farmer John" Then
        tx = tx & ", " & WorksheetFunction.TextJoin(", ", True, FarmerArray(ArrayStart, 2), FarmerArray(ArrayStart, 3), FarmerArray(ArrayStart, 4))
    End If
Next ArrayStart
Worksheets("Farmers").Cells(1, 1) = Mid(tx, 3)
End With

Thanks Akuini, this works.

 

[Akuini]

You're welcome.

 

[Akuini]

Sorry, I forgot something.
If it's possible that the filter produces no results, use this one instead, to avoid error:

Dim tx As String
For ArrayStart = 1 To 4
    If FarmerArray(ArrayStart, 1) = "Farmer John" Then
        tx = tx & ", " & WorksheetFunction.TextJoin(", ", True, FarmerArray(ArrayStart, 2), FarmerArray(ArrayStart, 3), FarmerArray(ArrayStart, 4))
    End If
Next ArrayStart

If tx = "" Then
    Worksheets("Farmers").Cells(1, 1) = ""
Else
    Worksheets("Farmers").Cells(1, 1) = Mid(tx, 3)
End If

 

[BritsBlitz]

Sorry, I forgot something.
If it's possible that the filter produces no results, use this one instead, to avoid error:

Dim tx As String
For ArrayStart = 1 To 4
    If FarmerArray(ArrayStart, 1) = "Farmer John" Then
        tx = tx & ", " & WorksheetFunction.TextJoin(", ", True, FarmerArray(ArrayStart, 2), FarmerArray(ArrayStart, 3), FarmerArray(ArrayStart, 4))
    End If
Next ArrayStart

If tx = "" Then
    Worksheets("Farmers").Cells(1, 1) = ""
Else
    Worksheets("Farmers").Cells(1, 1) = Mid(tx, 3)
End If

Hi Akuini

I didn't use your code verbatim since it's a piece that goes into a much larger code, but I used your concept and changed it to work for what I need. I was struggling for a while trying to convert a formula with "TextJoin" and "Filter" into an equivalent VBA code and your suggestion to use tx = tx & ", " & WorksheetFunction.TextJoin(", ", True, FarmerArray(ArrayStart, 2), FarmerArray(ArrayStart, 3), FarmerArray(ArrayStart, 4)) made me abandon the filter command and get me to what I needed.

For reference, this is the formula I started with:
=IFERROR(TEXTJOIN(", ",TRUE,"<"&((FILTER(RULES!G2:G299,(RULES!I2:I299="Yes")*(RULES!A2:A299=C58)))) &" -("&(FILTER(RULES!E2:E299,(RULES!I2:I299="Yes")*(RULES!A2:A299=C58)))&")- "&((FILTER(RULES!F2:F299,(RULES!I2:I299="Yes")*(RULES!A2:A299=C58))))&">"),"")

This is the VBA code I ended up with based on your suggestion:

If IsEmpty(suggestArray(ruleslinestart, 2)) Or IsEmpty(suggestArray(ruleslinestart, 3)) Or IsEmpty(suggestArray(ruleslinestart, 4)) Then
    Else
    nodeconnections = nodeconnections & ", " & WorksheetFunction.TextJoin(", ", True, "<" & suggestArray(ruleslinestart, 2) & " -(" & suggestArray(ruleslinestart, 3) & ")- " _
    & suggestArray(ruleslinestart, 4) & ">")
    Worksheets("5G NETWORK").Cells(networklinestart, 9).Value = Mid(nodeconnections, 3)
End If