# Cell contains several of many text strings then return multiple lookup values



## MHamid (Oct 13, 2022)

Hello,
I am wondering if someone would know if this would be possible. I have two sheets in a workbook. One is my "Main" sheet and the other is "Key" sheet. In my "Main" sheet, I have a column where the cell value is a string with various codes with an without the parenthesis. I would like to search through that string based on a key search using a range column (column A) in the "Key" sheet and would like to bring back a result found in column D of the "Key" sheet. Would this be possible using a formula or VBA code. I would prefer a VBA code, but I would accept either at this point. 
I cannot provide the actual data, but below is a what I am looking for. One of the issues I have is that I need to find the exact match. For instance, if I am looking for 12345. I expect to match 12345 with 12345 ... 12345 should not be match to 1234567.

"Main" sheet: Column A has the string cell to be searched through for matches and column B shows the results that I would like to see. 

*Managed Segment (Home)-Node Number**Business Name*Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678Business/TechnologyBusiness 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 2a [L7](78945) - 78945Business/Technology/OperationsBusiness 2 [L7](96385) - 96385BusinessBusiness 3 [L5] - 1234567OperationsBusiness 3 - 1234567;Business 4 - 45612Operations/Technology


"Key" Sheet: Houses the search/lookup values in column A and the result I want brought back is in Column D.

*MS ID**MS ID Name**Sector**Business Name*(12345)Business 1 [L6]sector1Business(45678)Business 2 [L7]sector2Technology(78945)Business 2a [L7]sector3Operations(96385)Business 2 [L7]sector1Business(1234567)Business 3sector3Operations(45612)Business 4sector2Technology12345Business 1 [L6]sector1Business45678Business 2 [L7]sector2Technology78945Business 2a [L7]sector3Operations96385Business 2 [L7]sector1Business1234567Business 3sector3Operations45612Business 4sector2Technology


Let me know if more information is needed.


----------



## MHamid (Nov 8, 2022)

Hello,

I have the below code for my original post. It works, but it only provides result for the first instance. I need it to give a result for all items listed in the cell within the text string cell. As in "Result 1 - Result 2 - Result 3" , ETC. 
Let me know if you need any further information.


```
Sub app_functions_OFF()
'Turn off excel features to speed up calculations

    Application.Calculation = xlManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    
End Sub
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub app_functions_ON()
'Turn on excel features to speed up calculations

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    Application.DisplayStatusBar = True
    
End Sub
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub HomeNodeNumber()
app_functions_OFF
'object alignment
Dim y As Long 'row counter  +1
Dim a As Long
Dim wb_data As Worksheet
Dim wb_Key As Worksheet
Dim source_col As Long 'column number
Dim destin_col As Long 'destination column number
Dim destin_col2 As Long 'destination column number 2
Dim grp_names_1() As Variant
Dim grp_names_2() As Variant
Dim grp_names_3() As Variant

Dim as_of_date As Date

source_col = 6 'Managed Segment (Home)-Node Number
destin_col = 27 ' Object_L1
destin_col2 = 28 ' Object_L2


Set wb_data = Sheets("Main")
Set wb_Key = Sheets("Key")

grp_names_1 = Sheets("Key").Range("A2:A11211").Value2
grp_names_2 = Sheets("Key").Range("C2:C11211").Value2
grp_names_3 = Sheets("Key").Range("D2:D11211").Value2


lrow = wb_data.Cells(Rows.Count, 1).End(xlUp).Row



        For x = 1 To lrow
            Set cur_level = Nothing
            If x <= lrow Then
                y = x + 1
                cur_level = wb_data.Cells(y, source_col).Value
                For a = 1 To UBound(grp_names_1)
                    If InStr(LCase(cur_level), LCase(grp_names_1(a, 1))) <> 0 Then
                        wb_data.Cells(y, destin_col) = grp_names_2(a, 1)
                        wb_data.Cells(y, destin_col2) = grp_names_3(a, 1)
                    End If
                Next a
            End If
        Next x


app_functions_ON
End Sub
```

Thank you,


----------



## MHamid (Nov 29, 2022)

Hello,

I have tried a For Each statement to loop through each number within the cell string after the semicolon ( ; ), but I am not able to get my desired result (Business/Technology). I only see a result for the first number in the cell string. I have attached a test file. Any assistance will be greatly appreciated. 


Main Sheet:
Testing.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC1Account NumberBL1BL2BL3BL4MS - Node NumberMS - HomeImpct Node numberImpact HomeBusiness 1Business 2Business 3Business 4Business 5Business 6Business 7Business 8Business 9Business 10Business 11Business 12Business 13Business 14Business 15Business 16Business 17Business 18AreaBusiness Name2S123456GBBusiness 1Business 1Business 1Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678other dataother dataother data3S456789ETBusiness 2Business 2Business 2Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 2a [L7](78945) - 78945other dataother dataother data4S789123IGBusiness 3Business 3Business 3Business 2 [L7](96385) - 96385other dataother dataother data5S456123PBBusiness 4Business 4Business 4Business 3 [L5] - 1234567other dataother dataother data6S741852GBBusiness 5Business 5Business 5Business 3 - 1234567;Business 4 - 45612other dataother dataother dataMain

Key Sheet:
Testing.xlsmABCD1ID #ID NameAreaBusiness Name2(12345)Business 1 [L6]GBBusiness3(45678)Business 2 [L7]ETTechnology4(78945)Business 2a [L7]IGOperations5(96385)Business 2 [L7]GBBusiness6(1234567)Business 3 [L5]PBOperations7(45612)Business 4 [L6]ETTechnology812345Business 1 [L6]GBBusiness945678Business 2 [L7]ETTechnology1078945Business 2a [L7]IGOperations1196385Business 2 [L7]GBBusiness121234567Business 3 [L5]PBOperations1345612Business 4 [L6]ETTechnologyKey


Updated Code:

```
Sub app_functions_OFF()
'Turn off excel features to speed up calculations

    Application.Calculation = xlManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    
End Sub
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub app_functions_ON()
'Turn on excel features to speed up calculations

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    Application.DisplayStatusBar = True
    
End Sub
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub HomeNodeNumber()
app_functions_OFF
'object alignment
Dim y As Long 'row counter  +1
Dim a As Long
Dim wb_data As Worksheet
Dim wb_Key As Worksheet
Dim source_col As Long 'column number
Dim destin_col As Long 'destination column number
Dim destin_col2 As Long 'destination column number 2
Dim grp_names_1() As Variant
Dim grp_names_2() As Variant
Dim grp_names_3() As Variant

Dim Item As Variant

source_col = 6 'Managed Segment (Home)-Node Number
destin_col = 27 ' Object_L1
destin_col2 = 28 ' Object_L2


Set wb_data = Sheets("Main")
Set wb_Key = Sheets("Key")

grp_names_1 = Sheets("Key").Range("A2:A11211").Value2
grp_names_2 = Sheets("Key").Range("C2:C11211").Value2
grp_names_3 = Sheets("Key").Range("D2:D11211").Value2


lrow = wb_data.Cells(Rows.Count, 1).End(xlUp).Row



        For x = 1 To lrow
            Set cur_level = Nothing
            If x <= lrow Then
                y = x + 1
                cur_level = wb_data.Cells(y, source_col).Value
                For a = 1 To UBound(grp_names_1)
                    For Each Item In Split(source_col, ";")
                        If InStr(LCase(cur_level), LCase(grp_names_1(a, 1))) <> 0 Then
                            wb_data.Cells(y, destin_col) = grp_names_2(a, 1)
                            wb_data.Cells(y, destin_col2) = grp_names_3(a, 1)
                        End If
                     Next Item
                Next a
            End If
        Next x


app_functions_ON
End Sub
```

Thank you,


----------



## MHamid (Dec 1, 2022)

Anyone able to provide any guidance to the problem I am having? I cant seem to figure out what I am missing.


----------



## Peter_SSs (Dec 1, 2022)

MHamid said:


> I would prefer a VBA code,


If that is the case, would it be acceptable for your Windows operating system only?

Also, do you need to have all those MS ID's listed twice (once with brackets and once without)? The method that I am contemplating would only need them one way or the other (preferably without brackets).


----------



## MHamid (Dec 5, 2022)

Hi Peter,



Peter_SSs said:


> If that is the case, would it be acceptable for your Windows operating system only?


Not sure what you mean here, but the VBA code is used in a Windows operating system only. 



Peter_SSs said:


> Also, do you need to have all those MS ID's listed twice (once with brackets and once without)? The method that I am contemplating would only need them one way or the other (preferably without brackets).


My preference would be to have the MS IDs listed once (without the parenthesis/brackets). my concern is that it finds an exact match of the number. For instance, if I need to find an MS ID of 4567 then I will expect  to match with 4567 and not with 1234567. Does that make sense?
What method are you thinking?


Thank you,


----------



## Peter_SSs (Dec 6, 2022)

MHamid said:


> My preference would be to have the MS IDs listed once (without the parenthesis/brackets).


Great, that suits my idea.



MHamid said:


> Not sure what you mean here,


What I mean is that the vba code that I have suggested below will not work on a Mac.

Here is my 'Key' sheet - IDs listed without parentheses

MHamid.xlsmABCD1MS IDMS ID NameSectorBusiness Name212345Business 1 [L6]sector1Business345678Business 2 [L7]sector2Technology478945Business 2a [L7]sector3Operations596385Business 2 [L7]sector1Business61234567Business 3sector3Operations745612Business 4sector2TechnologyKey


```
Sub BusinessNames()
  Dim RX As Object, M As Object, d As Object, d2 As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^|\D)(\d+)(\D|$)"
  With Sheets("Key")
    a = .Range("A2", .Range("D" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(CStr(a(i, 1))) = a(i, 4)
  Next i
  With Sheets("Main")
    a = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      d2.RemoveAll
      For Each M In RX.Execute(a(i, 1))
        If d.exists(M.submatches(1)) Then
          If Not d2.exists(M.submatches(1)) Then
            d2(M.submatches(1)) = 1
            b(i, 1) = IIf(IsEmpty(b(i, 1)), "", b(i, 1) & "/") & d(M.submatches(1))
          End If
        End If
      Next M
    Next i
    .Range("B2").Resize(UBound(b)).Value = b
  End With
End Sub
```

Here is my 'Main' sheet - sample data in column A and column B is the result of the above code. Is that what you are after?

MHamid.xlsmAB1Managed Segment (Home)-Node NumberBusiness Name2Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678Business/Technology3Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 2a [L7](78945) - 78945Business/Technology/Operations4Business 2 [L7](96385) - 96385Business5Business x - 999996Business 3 [L5] - 1234567;Business 2 [L7](11111) - 11111;Business 2a [L7](78945) - 78945Operations/Operations7Business 3 - 1234567;Business 4 - 45612Operations/TechnologyMain


----------



## MHamid (Dec 6, 2022)

Hi Peter, 

This works great. I am getting duplicates, but I think it's ok. 
Quick question though. How can I manipulate the code to use the MS ID Name column instead from the Key sheet? I ask because I need to do a similar search for another column based on the MS ID Name column. I tried to adjust the columns but the code stops working at the below line.

```
d(CStr(a(i, 1))) = a(i, 4)
```

Updated code to the below in an attempt to use the MS ID Name

```
Sub MSHome()
  Dim RX As Object, M As Object, d As Object, d2 As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^|\D)(\d+)(\D|$)"
  With Sheets("Key")
    a = .Range("B2", .Range("D" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(CStr(a(i, 1))) = a(i, 4)
  Next i
  With Sheets("Main")
    a = .Range("G3", .Range("G" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      d2.RemoveAll
      For Each M In RX.Execute(a(i, 1))
        If d.exists(M.submatches(1)) Then
          If Not d2.exists(M.submatches(1)) Then
            d2(M.submatches(1)) = 1
            b(i, 1) = IIf(IsEmpty(b(i, 1)), "", b(i, 1) & "/") & d(M.submatches(1))
          End If
        End If
      Next M
    Next i
    .Range("AC3").Resize(UBound(b)).Value = b
  End With
End Sub
```

thank you,


----------



## Peter_SSs (Dec 6, 2022)

MHamid said:


> I am getting duplicates, but I think it's ok.


Do you mean like in cell B6 in my previous post where two 'Operations' were derived from two different IDs (1234567 & 78945)? If you prefer they not be duplicated if that happens, it could be arranged.



MHamid said:


> How can I manipulate the code to use the MS ID Name column instead


I can see some issues with that if the sample data above is at all realistic. For example 

If MS ID Name of "Business 2 [L7]" was found, how would we know whether to return "Technology" from cell D3 of 'Key' or "Business" from cell D5 of 'Key'?


Is it possible that MS ID Names could be, for example, "Business 3" and "Business 3 [L5]"? If that was possible it might be tricky to decide exactly where a MS ID Name starts and finishes.

Perhaps you could give some new sample, but representative, data and expected results explaining the new requirement?


----------



## MHamid (Dec 7, 2022)

Hi Peter,



Peter_SSs said:


> Do you mean like in cell B6 in my previous post where two 'Operations' were derived from two different IDs (1234567 & 78945)? If you prefer they not be duplicated if that happens, it could be arranged.


Yes, that is correct. If duplicates can be removed that would be great!



Peter_SSs said:


> If MS ID Name of "Business 2 [L7]" was found, how would we know whether to return "Technology" from cell D3 of 'Key' or "Business" from cell D5 of 'Key'?


That's not realistic data. The MS ID Name wouldn't be duplicate like i mistakenly did in my sample. 



Peter_SSs said:


> Is it possible that MS ID Names could be, for example, "Business 3" and "Business 3 [L5]"? If that was possible it might be tricky to decide exactly where a MS ID Name starts and finishes.


It is extremely rare to have an MS ID Name without the "[L#]" after the name. 99.9% of the time the MS ID Name will be like "Business 3 [L5]". I would prefer the focus to be on the business names that are like "Business 3 [L5]" and ignore any that are not a match or are blank.

Below is a sample with the focus in Column G of the Main sheet. the desired result is in column AC. 
Testing.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC1Account NumberBL1BL2BL3BL4MS - Node NumberMS - HomeImpct Node numberImpact HomeBusiness 1Business 2Business 3Business 4Business 5Business 6Business 7Business 8Business 9Business 10Business 11Business 12Business 13Business 14Business 15Business 16Business 17Business 18AreaBusiness Name2S123456GBBusiness 1Business 1Business 1Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678Business 1 [L6] 12345 -- Business 1a [L5] ### -- Business 1b [L4] ### -- Business 1c [L3] ### -- Business 1d [L2] ### -- Business 1e [L1];Business 2 [L7] 45678 --  Business 2a [L6] ### -- Business 2b [L5] ### -- Business 2c [L4] ### -- Business 2d [L3] ### -- Business 2e [L2] -- Business 2f [L1]other dataother dataGB/ETBusiness/Technology3S456789ETBusiness 2Business 2Business 2Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 6 [L5](78945) - 78945Business 1 [L6] 12345 -- Business 1a [L5] ### -- Business 1b [L4] ### -- Business 1c [L3] ### -- Business 1d [L2] ### -- Business 1e [L1];Business 2 [L7] 45678 --  Business 2a [L6] ### -- Business 2b [L5] ### -- Business 2c [L4] ### -- Business 2d [L3] ### -- Business 2e [L2] -- Business 2f [L1];Business 6 [L5] 78945 -- Business 6a [L4] ### -- Business 6b [L3] ### -- Business 6c [L2] ### -- Business 6d [L1] other dataother dataGB/ET/IGBusiness/Technology/Operations4S789123IGBusiness 3Business 3Business 3Business 5 [L7](96385) - 96385Business 5 [L7] 96385 -- Business 5a [L6] ### -- Business 5b [L5] ### -- Business 5c [L4] ### -- Business 5d [L3] ### -- Business 5e [L2] -- Business 5f [L1]other dataother dataGBBusiness5S456123PBBusiness 4Business 4Business 4Business 3 [L5] - 1234567Business 3 [L5] 1234567 -- Business 3a [L4] ### -- Business 3b [L3] ### -- Business 3c [L2] -- Business 3d [L1]other dataother dataPBOperations6S852741PBBusiness 4Business 4Business 47S741963ETBusiness 5Business 5Business 5Business 3 [L5]- 1234567;Business 4 [L6]- 45612Business 3 [L5] 1234567 -- Business 3a [L4] ### -- Business 3b [L3] ### -- Business 3c [L2] -- Business 3d [L1];Business 4 [L6] 45612 --Business 4a [L5] ### -- Business 4b [L4] ### -- Business 4c [L3] ### -- Business 3d [L2] -- Business 4e [L1]other dataother dataPB/ETOperations/Technology8S741852GBBusiness 5Business 5Business 5siness 2 [L7](45678) - 45678;Business 5 [L7](96385) - 96385;Business 3 [L5]- 1234567;Business 4 [L6]- 45612Business 2 [L7] 45678 --  Business 2a [L6] ### -- Business 2b [L5] ### -- Business 2c [L4] ### -- Business 2d [L3] ### -- Business 2e [L2] -- Business 2f [L1];Business 5 [L7] 96385 -- Business 5a [L6] ### -- Business 5b [L5] ### -- Business 5c [L4] ### -- Business 5d [L3] ### -- Business 5e [L2] -- Business 5f [L1];Business 3 [L5] 1234567 -- Business 3a [L4] ### -- Business 3b [L3] ### -- Business 3c [L2] -- Business 3d [L1];Business 4 [L6] 45612 --Business 4a [L5] ### -- Business 4b [L4] ### -- Business 4c [L3] ### -- Business 3d [L2] -- Business 4e [L1]other dataother dataPB/ETTechnology/Business/OperationsMain

The below sample of the Key, the focus is in column B. 
Testing.xlsmABCD1ID #ID NameAreaBusiness Name212345Business 1 [L6]GBBusiness345678Business 2 [L7]ETTechnology478945Business 6 [L5]IGOperations596385Business 5 [L7]GBBusiness61234567Business 3 [L5]PBOperations745612Business 4 [L6]ETTechnologyKey


Please let me know if I need to further explain. 

Thank you,


----------



## MHamid (Oct 13, 2022)

Hello,
I am wondering if someone would know if this would be possible. I have two sheets in a workbook. One is my "Main" sheet and the other is "Key" sheet. In my "Main" sheet, I have a column where the cell value is a string with various codes with an without the parenthesis. I would like to search through that string based on a key search using a range column (column A) in the "Key" sheet and would like to bring back a result found in column D of the "Key" sheet. Would this be possible using a formula or VBA code. I would prefer a VBA code, but I would accept either at this point. 
I cannot provide the actual data, but below is a what I am looking for. One of the issues I have is that I need to find the exact match. For instance, if I am looking for 12345. I expect to match 12345 with 12345 ... 12345 should not be match to 1234567.

"Main" sheet: Column A has the string cell to be searched through for matches and column B shows the results that I would like to see. 

*Managed Segment (Home)-Node Number**Business Name*Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678Business/TechnologyBusiness 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 2a [L7](78945) - 78945Business/Technology/OperationsBusiness 2 [L7](96385) - 96385BusinessBusiness 3 [L5] - 1234567OperationsBusiness 3 - 1234567;Business 4 - 45612Operations/Technology


"Key" Sheet: Houses the search/lookup values in column A and the result I want brought back is in Column D.

*MS ID**MS ID Name**Sector**Business Name*(12345)Business 1 [L6]sector1Business(45678)Business 2 [L7]sector2Technology(78945)Business 2a [L7]sector3Operations(96385)Business 2 [L7]sector1Business(1234567)Business 3sector3Operations(45612)Business 4sector2Technology12345Business 1 [L6]sector1Business45678Business 2 [L7]sector2Technology78945Business 2a [L7]sector3Operations96385Business 2 [L7]sector1Business1234567Business 3sector3Operations45612Business 4sector2Technology


Let me know if more information is needed.


----------



## Peter_SSs (Dec 9, 2022)

Give this one a try


```
Sub BusinessNames_v2()
  Dim RX As Object, M As Object, d As Object, d2 As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  Dim s As String, sM As String
  
  Set d = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  With Sheets("Key")
    RX.Pattern = "(\b)(" & Join(Application.Transpose(.Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value), "|") & ")"
    RX.Pattern = Replace(Replace(RX.Pattern, "[", "\["), "]", "\]")
    a = .Range("B2", .Range("D" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(CStr(a(i, 1))) = a(i, 3)
  Next i
  With Sheets("Main")
    a = .Range("G2", .Range("G" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      d2.RemoveAll
      s = vbNullString
      For Each M In RX.Execute(a(i, 1))
        sM = M
        If Not d2.exists(d(sM)) Then
          s = s & "/" & d(sM)
          d2(d(sM)) = 1
        End If
      Next M
      b(i, 1) = Mid(s, 2)
    Next i
    .Range("AC2").Resize(UBound(b)).Value = b
  End With
End Sub
```


----------



## MHamid (Dec 9, 2022)

Hi Peter,

I am getting the following error message when I run the code.
"Application-defined or object-defined error" and the below is highlighted in yellow in the code. 


```
For Each M In RX.Execute(a(i, 1))
```


Thank you,


----------



## Peter_SSs (Dec 10, 2022)

MHamid said:


> I am getting the following error message when I run the code.
> "Application-defined or object-defined error" and the below is highlighted in yellow in the code.
> 
> 
> ...


The code worked for me with the sample data provide in post #10. 

Have you tried the code with that same sample data?
When you get the error and click Debug, what value shows in the pop-up when you hover over M in that yellow line?
What value shows in the pop-up when you hover over i in that yellow line?


----------



## MHamid (Dec 16, 2022)

Hi Peter,



Peter_SSs said:


> Have you tried the code with that same sample data?


I did try the code with the same sample data and it works fine.



Peter_SSs said:


> When you get the error and click Debug, what value shows in the pop-up when you hover over M in that yellow line?


When I hover over it, it states M=Nothing



Peter_SSs said:


> What value shows in the pop-up when you hover over i in that yellow line?


it states i = 1

This is what I see in the Locals window:






When I copied the data to my sheet, the only changes I made to it was in the Main Sheet section. I updated G2 to G3 and then updated AC2 to AC3 because that's where my data starts. I made the same changes in the sample data and it worked fine in the sample data, but for some reason it's still not working with my actual data.
Can you explain how the macro works? Maybe it's something I'm missing with the actual data.


Thank you,


----------



## MHamid (Dec 16, 2022)

MHamid said:


> Hi Peter,
> 
> 
> I did try the code with the same sample data and it works fine.
> ...


Not sure if this might help, but the data I have in cell G3 of the Main sheet is like this: 
Global Markets Treasury [L6] 26395 -- Fixed Income Currencies and Commodities [L5] 29414 -- Global Markets [L4] 5 -- Institutional Clients Group [L3] 3 -- Core Citi Businesses [L2] 20448 -- Total Citi [L1] 1;International Treasury [L7] 22440 -- Treasury [L6] 9905121 -- Finance [L5] 5000 -- Global Functions and Other Corporate Activities [L4] 4923 -- Enterprise O&T Global Functions & Other Corporate [L3] 25302 -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1

Would that make a difference in your code? I ask because the error seems to be stating that the object doe snot exist, but there is data in cell G3.


----------



## MHamid (Dec 16, 2022)

Sorry, I also just added the expressions for that line in the watchlist and received the below results. Not sure exactly how to fix.


----------



## Peter_SSs (Dec 16, 2022)

If it works for the sample data but not for your real data then it is almost certainly related to your data and how it differs from the sample.
My suspicion is the inclusion in ID Names of other special characters that are used in regular expression syntax which need modification as I did with the "[" and "]" symbols in this line

```
RX.Pattern = Replace(Replace(RX.Pattern, "[", "\["), "]", "\]")
```
Another possibility though is it could relate to any changes you have made to the code.



MHamid said:


> the data I have in cell G3 of the Main sheet is like this:
> Global Markets Treasury [L6] 26395 -- Fixed Income Currencies and Commodities [L5] 29414 -- Global Markets [L4] 5 -- Institutional Clients Group [L3] 3 -- Core Citi Businesses [L2] 20448 -- Total Citi [L1] 1;International Treasury [L7] 22440 -- Treasury [L6] 9905121 -- Finance [L5] 5000 -- Global Functions and Other Corporate Activities [L4] 4923 -- Enterprise O&T Global Functions & Other Corporate [L3] 25302 -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1


So can you post (with XL2BB) the range G1:G4 from 'Main' and columns B:D (but hide column C) from 'Key' and then also post the modified code?


----------



## MHamid (Dec 19, 2022)

Hi Peter,

Here is the updated data.
Testing.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC1Account NumberBL1BL2BL3BL4MS - Node NumberMS - HomeImpct Node numberImpact HomeBusiness 1Business 2Business 3Business 4Business 5Business 6Business 7Business 8Business 9Business 10Business 11Business 12Business 13Business 14Business 15Business 16Business 17Business 18AreaBusiness Name2S123456GBBusiness 1Business 1Business 1Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678Global Markets Treasury [L6] 26395 -- Fixed Income Currencies and Commodities [L5] 29414 -- Global Markets [L4] 5 -- Institutional Clients Group [L3] 3 -- Core Citi Businesses [L2] 20448 -- Total Citi [L1] 1;OOBC [L7] -- ER [L6] -- COO [L5] -- EO&T [L4] -- EiO&T GF & OC [L3] -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1other dataother data3S456789ETBusiness 2Business 2Business 2Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 6 [L5](78945) - 78945Global Markets Treasury [L6] 26395 -- Fixed Income Currencies and Commodities [L5] 29414 -- Global Markets [L4] 5 -- Institutional Clients Group [L3] 3 -- Core Citi Businesses [L2] 20448 -- Total Citi [L1] 1;OOBC [L7] -- ER [L6] -- COO [L5] -- EO&T [L4] -- EiO&T GF & OC [L3] -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1; FRSS Mng [L7] 278003 -- Finanac eand Risk Shared Services [L6] 9908260 -- Total Citi Shared Services [L5] 8163 -- Enterprise Operations & Technology[L4] 5497 -- -- Enterprise O&T Global Functions & Other Corporate [L3] 25302 -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1other dataother data4S789123IGBusiness 3Business 3Business 3Business 5 [L7](96385) - 96385Treasury [L6] -- Finance [L5] -- Global Markets [L4] -- EIO&T [L3] -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1other dataother data5S456123PBBusiness 4Business 4Business 4Business 3 [L5] - 1234567International Treasury [L7] 22440 -- Treasury [L6] 9905121 -- Finance [L5] 5000 -- Global Functions and Other Corporate Activities [L4] 4923 -- Enterprise O&T Global Functions & Other Corporate [L3] 25302 -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1Liquidity [L7] 9921639 -- TTS CO [L6] 4349 -- DEF Ops [L5] 22939 -- DEF O&T [L4] 23078 -- DEF [L3] 3 -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1other dataother data6S852741PBBusiness 4Business 4Business 47S741963ETBusiness 5Business 5Business 5Business 3 [L5]- 1234567;Business 4 [L6]- 45612OOBC [L7] -- ER [L6] -- COO [L5] -- EO&T [L4] -- EiO&T GF & OC [L3] -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1;Liquidity [L7] 9921639 -- TTS CO [L6] 4349 -- ABC Ops [L5] 22939 -- ABC O&T [L4] 23078 -- ABC [L3] 3 -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1other dataother data8S741852GBBusiness 5Business 5Business 5siness 2 [L7](45678) - 45678;Business 5 [L7](96385) - 96385;Business 3 [L5]- 1234567;Business 4 [L6]- 45612International Treasury [L7] 22440 -- Treasury [L6] 9905121 -- Finance [L5] 5000 -- Global Functions and Other Corporate Activities [L4] 4923 -- Enterprise O&T Global Functions & Other Corporate [L3] 25302 -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1;Treasury [L6] -- Fiinance [L5] -- Global Markets [L4] -- EIO&T [L3] -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1;OOBC [L7] -- ER [L6] -- COO [L5] -- EO&T [L4] -- EiO&T GF & OC [L3] -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1;Liquidity [L7] 9921639 -- TTS CO [L6] 4349 -- ABC Ops [L5] 22939 -- ABC O&T [L4] 23078 -- ABC [L3] 3 -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1other dataother dataMain

Testing.xlsmABD1ID #ID NameBusiness Name212345Global Markets Treasury [L6]Business345678Total Citi Shared Services [L5]Technology478945ABC Ops [L5]Operations596385Finance [L5]Business61234567DEF Ops [L5]Operations745612COO [L5]TechnologyKey


i'm still working on the pattern to see if that is the issue. Also, the only thing I updated in the code was just the range in the main sheet from G2 to G3 and AC2 to AC3. I didn't change anything else in the code. 

Let me know if you are getting an error with the updated data I provided.



Thank you,


----------



## Peter_SSs (Dec 19, 2022)

MHamid said:


> Let me know if you are getting an error with the updated data I provided.


I don't get any error if I use that sample data in the two sheets.
Have you tried it with that sample data only?

How many rows do you actually have in the 'Key' sheet? If it is not too many (& the data is not sensitive) can you post that? I suspect any problem will lie with one or more of those actual values.

BTW, when posting XL2BB sample data, only post the relevant columns. For example, with Main you have post 29 columns when all we really needed was column G.
If you did want to show, say column G and column AC, then hide all the columns between them before creating your mini sheet.


----------



## MHamid (Dec 20, 2022)

Peter_SSs said:


> I don't get any error if I use that sample data in the two sheets.
> Have you tried it with that sample data only?
> 
> How many rows do you actually have in the 'Key' sheet? If it is not too many (& the data is not sensitive) can you post that? I suspect any problem will lie with one or more of those actual values.
> ...



I tried it with the sample data and I didn't get an error. 

In the 'Key' sheet, there are currently 5605 rows of data. I was going through the process of elimination to see at what point the code will stop working and found what was causing the error. There was one data point listed as BusinessName( [L1].  The business name was listed with just one parentheses without a closure. Is there any way to avoid something like this causing an error. Could we add something within the code to state that if there is only 1 instance of a parentheses to remove it without affecting any other data that has the open and close parentheses?

My apologies for the data I provided with the XL2BB. I thought I did hide those columns. I will ensure to provide relevant data. 


Thank you,


----------



## MHamid (Oct 13, 2022)

Hello,
I am wondering if someone would know if this would be possible. I have two sheets in a workbook. One is my "Main" sheet and the other is "Key" sheet. In my "Main" sheet, I have a column where the cell value is a string with various codes with an without the parenthesis. I would like to search through that string based on a key search using a range column (column A) in the "Key" sheet and would like to bring back a result found in column D of the "Key" sheet. Would this be possible using a formula or VBA code. I would prefer a VBA code, but I would accept either at this point. 
I cannot provide the actual data, but below is a what I am looking for. One of the issues I have is that I need to find the exact match. For instance, if I am looking for 12345. I expect to match 12345 with 12345 ... 12345 should not be match to 1234567.

"Main" sheet: Column A has the string cell to be searched through for matches and column B shows the results that I would like to see. 

*Managed Segment (Home)-Node Number**Business Name*Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678Business/TechnologyBusiness 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 2a [L7](78945) - 78945Business/Technology/OperationsBusiness 2 [L7](96385) - 96385BusinessBusiness 3 [L5] - 1234567OperationsBusiness 3 - 1234567;Business 4 - 45612Operations/Technology


"Key" Sheet: Houses the search/lookup values in column A and the result I want brought back is in Column D.

*MS ID**MS ID Name**Sector**Business Name*(12345)Business 1 [L6]sector1Business(45678)Business 2 [L7]sector2Technology(78945)Business 2a [L7]sector3Operations(96385)Business 2 [L7]sector1Business(1234567)Business 3sector3Operations(45612)Business 4sector2Technology12345Business 1 [L6]sector1Business45678Business 2 [L7]sector2Technology78945Business 2a [L7]sector3Operations96385Business 2 [L7]sector1Business1234567Business 3sector3Operations45612Business 4sector2Technology


Let me know if more information is needed.


----------



## Peter_SSs (Dec 20, 2022)

MHamid said:


> There was one data point listed as BusinessName( [L1].


Parentheses are the problem as they have a special meaning in Regular Expression syntax. None of your previous samples for the 'Key' sheet had any parentheses at all so I didn't know to allow for them. 

Is that ID Name a mistake or could the 'Main' sheet have a matching name with a single bracket like that?

In any case, give this one a try.


```
Sub BusinessNames_v4()
  Dim RX As Object, M As Object, d As Object, d2 As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  Dim s As String, sM As String
  
  Set d = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  With Sheets("Key")
    RX.Pattern = Join(Application.Transpose(.Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value), "|")
    RX.Pattern = "(\b)(" & Replace(Replace(Replace(Replace(RX.Pattern, "[", "\["), "]", "\]"), "(", "\("), ")", "\)") & ")"
    a = .Range("B2", .Range("D" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(CStr(a(i, 1))) = a(i, 3)
  Next i
  With Sheets("Main")
    a = .Range("G3", .Range("G" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      d2.RemoveAll
      s = vbNullString
      For Each M In RX.Execute(a(i, 1))
        sM = M
        If Not d2.exists(d(sM)) Then
          s = s & "/" & d(sM)
          d2(d(sM)) = 1
        End If
      Next M
      b(i, 1) = Mid(s, 2)
    Next i
    .Range("AC3").Resize(UBound(b)).Value = b
  End With
End Sub
```


----------



## MHamid (Thursday at 1:35 PM)

Hi Peter,

Happy New Year! I would have responded sooner, but was on vacation.
In any case, I just tested the updated code and it works. I just have to check the results to ensure nothing was missed or misaligned by an oversight in my part. 



Peter_SSs said:


> Parentheses are the problem as they have a special meaning in Regular Expression syntax. None of your previous samples for the 'Key' sheet had any parentheses at all so I didn't know to allow for them.
> 
> Is that ID Name a mistake or could the 'Main' sheet have a matching name with a single bracket like that?


Understood, that ID Name is not a mistake, but there is no match with a single parenthesis and a bracket "( [ ".

Also, the first code you assisted with provides results with duplicates. Where in the code would I need to update to remove duplicate results?


```
Sub MSHomeNodeNumber()
  Dim RX As Object, M As Object, d As Object, d2 As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  Set d2 = CreateObject("Scripting.Dictionary")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^|\D)(\d+)(\D|$)"
  With Sheets("Key")
    a = .Range("A2", .Range("D" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(CStr(a(i, 1))) = a(i, 4)
  Next i
  With Sheets("Main")
    a = .Range("F3", .Range("F" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      d2.RemoveAll
      For Each M In RX.Execute(a(i, 1))
        If d.exists(M.submatches(1)) Then
          If Not d2.exists(M.submatches(1)) Then
            d2(M.submatches(1)) = 1
            b(i, 1) = IIf(IsEmpty(b(i, 1)), "", b(i, 1) & "/") & d(M.submatches(1))
          End If
        End If
      Next M
    Next i
    .Range("AB3").Resize(UBound(b)).Value = b
  End With
End Sub
```

Thank you,


----------



## Peter_SSs (Thursday at 6:41 PM)

MHamid said:


> .. the first code you assisted with provides results with duplicates. Where in the code would I need to update to remove duplicate results?


Could we have some new small sample data (from Main and Key) that demonstrates the problem using the code above?


----------

