Forum Discussion

Iron Contributor

May 18, 2022

Solved

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

I tried to create a vba code for this but was unsuccessful, hopefully i could get some help from here. Sub join() Dim ws As Worksheet Dim exportWb As Workbook Dim exportWs As Worksheet Set expo...

Macros and VBA

HansVogelaar

May 19, 2022

hrh_dash

Sorry

Sub join()
    Dim ws As Worksheet
    Dim exportWb As Workbook
    Dim exportWs As Worksheet
    Dim exportlastRow As Long
    Set ws = Sheet1
    Set exportWb = Workbooks.Open("C:\Users\desktop\export.xlsx")
    Set exportWs = exportWb.Sheets("Sheet1")
    exportlastRow = exportWs.Cells(exportWs.Rows.Count, "E").End(xlUp).Row
    ThisWorkbook.Activate
    ws.Range("C10") = ws.Evaluate("TEXTJOIN("", "",TRUE,IF([export.XLSX]Sheet1!E2:E" & _
        exportlastRow & "=C15,[export.XLSX]Sheet1!A2:A" & exportlastRow & ",""""))")
End Sub

HansVogelaar

MVP

Jul 10, 2022

hrh_dash

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

hrh_dash

Iron Contributor

Jul 12, 2022

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
Iron Contributor
Jul 13, 2022
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.
HansVogelaar
MVP
Jul 12, 2022
hrh_dash

It's not clear to me what you want to do with "In Dunning" and "Active".
hrh_dash
Iron Contributor
Jul 12, 2022
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.
HansVogelaar
MVP
Jul 12, 2022
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.