Is there a way to have a dynamic textjoin with IF condition lookup from another workbook VBA

HansVogelaar , the source file changes on a weekly basis and each file contains 200k++ rows of values. Therefore, there are chances whereby the same business registration number would have both "In Dunning" and "Active" values with the main criterion fulfills "BSCS".

Am not sure whether is there any excel formula/function which could do a look up in the same column for both "In Dunning" and "Active" values. Nevertheless, the FILTER formula works perfectly for now.

hrh_dash 

It's not clear to me what you want to do with "In Dunning" and "Active".

HansVogelaar , noted on this. The AND function does not work on the filter formula if column AA are both “In Dunning” and “Active”. Tested by typing out the formula itself. Really appreciate your help. Thanks for the guidance.

hrh_dash 

As I have mentioned several times before, AND won't work: a cell in column AA cannot be equal to "In Dunning" and to "Active" at the same time.

HansVogelaar , actually i would like to change the OR function to an AND function and if the source file does not have the Business Registration Number then it should be populated as empty.

I have amended the "+" to "*" ,removing a set of parentheses ( ) and inserting the blank statement ("""") and it did not populate any result.

With ws.Range("C12")
            .Formula = "=TEXTJOIN("", "",TRUE,FILTER(" & _
                rng3.Address(External:=True) & ",(" & _
                rng1.Address(External:=True) & "=C15)*(" & _
                rng2.Address(External:=True) & "=""BSCS"")*(" & _
                rng4.Address(External:=True) & "=""In Dunning"")*(" & _
                rng4.Address(External:=True) & "=""Active""),""""))" '----> changing the "+" to "*" ,removing a set of parentheses and adding """" criterion ( )
            
            .Value = .Value

hrh_dash 

1. The + does indeed work as OR. You also need an extra pair of parentheses ( ) around the whole OR part.
2. What exactly do you want to do with FALSE?

HansVogelaar , the code works perfectly. Appreciate the assist. This is my first time using the filter formula and thus went to read up a little on it. 

Would like to clarify few things regarding on the code that you have provided which it works by the way.

1. Am i right to conclude that the "+" in between "In Dunning" and "Active" was an OR formula?

 With ws.Range("C12")
            .Formula = "=TEXTJOIN("", "",TRUE,FILTER(" & _
                rng3.Address(External:=True) & ",(" & _
                rng1.Address(External:=True) & "=C15)*(" & _
                rng2.Address(External:=True) & "=""BSCS"")*((" & _
                rng4.Address(External:=True) & "=""In Dunning"")+(" & _ '---> Am i right to say the "+" was for an OR formula?
                rng4.Address(External:=True) & "=""Active""))))"
            .Value = .Value

2. If option 1 is right to say that the "+" was an OR formula, how can I replace with an AND formula (replacing "+" with "*" and inserting a "FALSE" statement like the code below) . The current code below is showing an error msg "Run time error '1004' Application-Defined or Object-Defined Error

 With ws.Range("C12")
            .Formula = "=TEXTJOIN("", "",TRUE,FILTER(" & _
                rng3.Address(External:=True) & ",(" & _
                rng1.Address(External:=True) & "=C15)*(" & _
                rng2.Address(External:=True) & "=""BSCS"")*((" & _
                rng4.Address(External:=True) & "=""In Dunning"")*(" & _
                rng4.Address(External:=True) & "=""Active""),FALSE)))" '----> this line of coding this resulting an error pop up
            .Value = .Value

Apologies for the multiple queries..

hrh_dash 

Does this work for you?

Sub join2()  'working for cell C11 & C12
    Dim ws As Worksheet
    Dim BSCSWb As Workbook
    Dim BSCSWs As Worksheet
    Dim BSCSlastRow As Long
    Dim path As String
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range

    Set ws = Sheet1
    path = ws.Range("A1").Value
    Set BSCSWb = Workbooks.Open(path)
    Set BSCSWs = BSCSWb.Sheets("Raw")
    BSCSlastRow = BSCSWs.Cells(BSCSWs.Rows.Count, "B").End(xlUp).Row
    Set rng1 = BSCSWs.Range("B2:B" & BSCSlastRow)
    Set rng2 = BSCSWs.Range("J2:J" & BSCSlastRow)
    Set rng3 = BSCSWs.Range("A2:A" & BSCSlastRow)
    Set rng4 = BSCSWs.Range("AA2:AA" & BSCSlastRow)

    If Range("A1") <> 0 Then
        With ws.Range("C11")
            .Formula = "=TEXTJOIN("", "",TRUE,IF((" & _
                rng1.Address(External:=True) & "=C15)*(" & _
                rng2.Address(External:=True) & "=""EBS"")*(" & _
                rng4.Address(External:=True) & "=""Active"")," & _
                rng3.Address(External:=True) & ",""""))"
            .Value = .Value
        End With

        With ws.Range("C12")
            .Formula = "=TEXTJOIN("", "",TRUE,FILTER(" & _
                rng3.Address(External:=True) & ",(" & _
                rng1.Address(External:=True) & "=C15)*(" & _
                rng2.Address(External:=True) & "=""BSCS"")*((" & _
                rng4.Address(External:=True) & "=""In Dunning"")+(" & _
                rng4.Address(External:=True) & "=""Active""))))"
            .Value = .Value
        End With
    Else
        MsgBox "Please click Folder icon to select file path to look up data on EBS and BSCS."
    End If
End Sub

HansVogelaar , i have attached both the source file with 200k+ of rows and the file containing the macros.

hrh_dash 

I'd have to see a workbook in which the code doesn't work.

HansVogelaar , The error #value! was due to a long range of values in the source file.

The source file was supposed to have at least 200k++ rows of data.

The code that you have provided works perfectly provided the source file has a short range of values.

Therefore is there another work around way for it? The previous textjoin code (before adding an additional criterion "In Dunning") that you have provided did work regardless if the source file has a long ranges of values or not.

I tried backtracking it by typing out the excel formula thinking that there could be some issues with the formula or the referencing. But it turns out that the formula works when the source file has a short range of values too.

hrh_dash 

This is the result when I run the macro on your workbook:

HansVogelaar , I have attached the source file. the file containing the macros is perfectly fine. Again, really appreciate the help.

hrh_dash 

Weird - it works correctly for me in a quick test.

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

HansVogelaar , it's still populating an error #value! in the cell

hrh_dash 

Try this

    Set rng1 = BSCSWs.Range("B2:B" & BSCSlastRow)
    Set rng2 = BSCSWs.Range("J2:J" & BSCSlastRow)
    Set rng3 = BSCSWs.Range("A2:A" & BSCSlastRow)
    Set rng4 = BSCSWs.Range("AA2:AA" & BSCSlastRow)

    ws.Range("C12") = ws.Evaluate("TEXTJOIN("", "",TRUE,IF((" & _
        rng1.Address(External:=True) & "=C15)*(" & _
        rng2.Address(External:=True) & "=""BSCS"")*((" & _
        rng4.Address(External:=True) & "=""In Dunning"")+(" & _
        rng4.Address(External:=True) & "=""Active""))," & _
        rng3.Address(External:=True) & ",""""))")

HansVogelaar , yes in column AA will have "In Dunning" and "Active". I only have read function to the file, so i am not able to edit or insert any columns or rows.. 

I tried using the excel formula, it did work but I have no idea how to go about for the vba code..

=TEXTJOIN(", ",TRUE,IF(C15=Sheet1!B:B,Sheet1!A:A,IF(Sheet1!J:J="BSCS",Sheet1!A:A,IF(Sheet1!AA:AA="In Dunning",Sheet1!A:A,IF(Sheet1!AA:AA="Active",Sheet1!A:A,"")))))

hrh_dash 

But a cell in column AA cannot have the values "In Dunning" and "Active" at the same time.

Do you perhaps want to include a row if the cell in column AA contains either "In Dunning" or "Active"?

HansVogelaar , i tried inserting another variable rng5 and set it as range.

Both rng4 and rng5 will be taking from Column AA. 

Result is still showing #value!

Not sure whether having an AND function would help but am clueless on how to insert the AND function.

    Set rng1 = BSCSWs.Range("B2:B" & BSCSlastRow)
    Set rng2 = BSCSWs.Range("J2:J" & BSCSlastRow)
    Set rng3 = BSCSWs.Range("A2:A" & BSCSlastRow)
    Set rng4 = BSCSWs.Range("AA2:AA" & BSCSlastRow)
    Set rng5 = BSCSWs.Range("AA2:AA" & BSCSlastRow)

ws.Range("C12") = ws.Evaluate("TEXTJOIN("", "",TRUE,IF((" & _
        rng1.Address(External:=True) & "=C15)*(" & _
        rng2.Address(External:=True) & "=""BSCS"")*(" & _
        rng5.Address(External:=True) & "=""In Dunning"")*(" & _
        rng4.Address(External:=True) & "=""Active"")," & _
        rng3.Address(External:=True) & ",""""))")