I think a change to vba is warranted, though there may still be some teething issues to work through. More on that later.
Here I am suggesting a user-defined function. The same function can be used to do both jobs as I understand them. To implement it ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula* as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)
VBA Code:
Function CheckWords(sGoals As String, rDic As Range) As Long
Dim RX As Object
Set RX = CreateObject("VBScript.RegExp")
RX.IgnoreCase = True
RX.Pattern = "\b(" & Replace(Replace(Application.TextJoin("|", 1, rDic), ", ", "|"), ",", "|") & ")\b"
If RX.Test(sGoals) Then CheckWords = 1
End Function
* The formula in column D is the one-to-one check. So D2 formula checks to see if any words from C2 exist in B2, D3 formula checks to see if any words from C3 exist in B3 etc.
The formula in column E checks if any words in the whole of Dic1 exist in each cell in column B. So E2 formula checks all words in C2:C6 to see if any are in B2, E3 formula checks all words in C2:C6 to see if any are in B3 etc.
| sobrien1234.xlsm |
|---|
|
|---|
| B | C | D | E |
|---|
| 1 | Goals | Dic1 | | |
|---|
| 2 | Establish SMSF for residential property investment. LRBA/borrowing advice, with cash flow analysis. Rollover your existing super account/s into your SMSF. Keep the existing insurances in the super account/s being rolled over. | LRBA, LBRA, Recourse | 1 | 1 |
|---|
| 3 | To purchase an investment property using your super by setting up a SMSF & utilising an LRBA. | HUB24, HUB 24 | 0 | 1 |
|---|
| 4 | Purchase a commercial property in your existing self-managed super fund, using a limited recourse borrowing arrangement. | Westpac, WBC | 0 | 1 |
|---|
| 5 | To purchase a commercial property in your existing self-managed super fund, using a limited recourse borrowing arrangement. | CFS, Colonial First State | 0 | 1 |
|---|
| 6 | Would like to buy business real property using current SMSF and LRBA. More control and minimise tax for super investments. Flexibility and ease of administration of super. Like to buy a property in super. Maximise superannuation benefits. | Aust super, Australian Super, Aus Super | 0 | 1 |
|---|
|
|---|
Possible Issues:
- Punctuation may still cause an issue so keep an eye out for that. We should be able to handle such issues if they arise though.
- At the moment, this udf uses the TEXTJOIN function which is only available in Excel 365 and Excel 2019 so if this is to be used with other versions it will need modification, but that can be done.
- TEXTJOIN has a text length limit. You mentioned that Dic1 could get quite long. When used as in column E, the function joins all the Dic1 values into a string. For the sample above, that string is
"LRBA, LBRA, Recourse|HUB24, HUB 24|Westpac, WBC|CFS, Colonial First State|Aust super, Australian Super, Aus Super"
That string is just 113 characters long but if it were to exceed 32,767 characters then textjoin, and hence the udf, would fail. I think the size of Dic1 that you mentioned (150 companies with several names each should be okay but again keep an eye out if the list gets very long). Again, we can handle this differently if that text length does become a problem.
