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

[MHamid]

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) - 45678	Business/Technology
Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 2a [L7](78945) - 78945	Business/Technology/Operations
Business 2 [L7](96385) - 96385	Business
Business 3 [L5] - 1234567	Operations
Business 3 - 1234567;Business 4 - 45612	Operations/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]	sector1	Business
(45678)	Business 2 [L7]	sector2	Technology
(78945)	Business 2a [L7]	sector3	Operations
(96385)	Business 2 [L7]	sector1	Business
(1234567)	Business 3	sector3	Operations
(45612)	Business 4	sector2	Technology
12345	Business 1 [L6]	sector1	Business
45678	Business 2 [L7]	sector2	Technology
78945	Business 2a [L7]	sector3	Operations
96385	Business 2 [L7]	sector1	Business
1234567	Business 3	sector3	Operations
45612	Business 4	sector2	Technology

Let me know if more information is needed.

 

[Peter_SSs]

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]

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.

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]

.. 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?

 

[MHamid]

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

Hello, the data is in post#7. That's where I first mentioned the duplicate results. Would you be able to use the data I provided in post#7?

 

[MHamid]

Hi,

I still need some assistance with the below code.
It is currently duplicating some results. I don't need matches to be duplicated. I just need the matches listed once. How can I fix this code to provide a unique result (no duplicates)?
I have duplicated businesses in column F (in sample data), where the result will go in column AB as a unique value. For example, the word "Business" should not be duplicated.

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

Sample Data.xlsx
	A					F	G	H	I																			AB
2	Account Number					MS - Node Number	MS - Home	Impct Node number	Impact Home																			Business Name
3	S123456					Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 1 [L6](12345) - 12345	Business 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 data	other data																			Business/Technology
4	S456789					Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 6 [L5](78945) - 78945; Business 2 [L7](45678) - 45678	Business 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 data	other data																			Business/Technology/Operations
5	S789123					Business 5 [L7](96385) - 96385	Business 5 [L7] 96385 -- Business 5a [L6] ### -- Business 5b [L5] ### -- Business 5c [L4] ### -- Business 5d [L3] ### -- Business 5e [L2] -- Business 5f [L1]	other data	other data																			Business
6	S456123					Business 3 [L5] - 1234567	Business 3 [L5] 1234567 -- Business 3a [L4] ### -- Business 3b [L3] ### -- Business 3c [L2] -- Business 3d [L1]	other data	other data																			Operations
7	S852741
8	S741963					Business 3 [L5]- 1234567;Business 4 [L6]- 45612;Business 3 [L5]- 1234567	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 data	other data																			Operations/Technology
9	S741852					siness 2 [L7](45678) - 45678;Business 5 [L7](96385) - 96385;Business 3 [L5]- 1234567;Business 4 [L6]- 45612	Business 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 data	other data																			Technology/Business/Operations
Main

Sample Data.xlsx
	W	X	Y	Z	AA	AB
1	Ownership	Main Focus	Owned Area	Region	Impacted	Area
2	Owned	Ops	Operations			PB
3	Owned	Ops	Multi			PB
4	Owned	Tech	Technolgy			ET
5	Owned	Tech	Multi			ET
6	Not Owned	Business				IG
7	Not Owned	Shared			Operations	GB
8	Not Owned	Shared			Technolgy	GB
Key

Thank you,

 

[Peter_SSs]

still need some assistance with the below code.
It is currently duplicating some results.

I am no longer immediately familiar with this code or the issues involved.
However, with the code and sample data that you have just provided I get no results at all, let alone duplicates so i don't know what to advise.
Also, from tomorrow I will rarely be on the forum for a few weeks.

 

[MHamid]

Hi,

Below is the Key sheet. i provided the wrong Key sheet data.
Can anyone else assist?

Sample Data.xlsx
	A	B	C
1	ID #	ID Name	Business Name
2	12345	Global Markets Treasury [L6]	Business
3	45678	Total Citi Shared Services [L5]	Technology
4	78945	ABC Ops [L5]	Operations
5	96385	Finance [L5]	Business
6	1234567	DEF Ops [L5]	Operations
7	45612	COO [L5]	Technology
Key