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]

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]

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]

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,

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

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

 

[MHamid]

Hi Peter,

1. Have you tried the code with that same sample data?

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

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

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]

Hi Peter,


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


When I hover over it, it states M=Nothing


it states i = 1

This is what I see in the Locals window:
View attachment 81057


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,

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]

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]

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.

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]

Hi Peter,

Here is the updated data.

Testing.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Account Number	BL1	BL2	BL3	BL4	MS - Node Number	MS - Home	Impct Node number	Impact Home	Business 1	Business 2	Business 3	Business 4	Business 5	Business 6	Business 7	Business 8	Business 9	Business 10	Business 11	Business 12	Business 13	Business 14	Business 15	Business 16	Business 17	Business 18	Area	Business Name
2	S123456	GB	Business 1	Business 1	Business 1	Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678	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;OOBC [L7] -- ER [L6] -- COO [L5] -- EO&T [L4] -- EiO&T GF & OC [L3] -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1	other data	other data
3	S456789	ET	Business 2	Business 2	Business 2	Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 6 [L5](78945) - 78945	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;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] 1	other data	other data
4	S789123	IG	Business 3	Business 3	Business 3	Business 5 [L7](96385) - 96385	Treasury [L6] -- Finance [L5] -- Global Markets [L4] -- EIO&T [L3] -- Corporate / Other [L2] 4921 -- Total Citi [L1] 1	other data	other data
5	S456123	PB	Business 4	Business 4	Business 4	Business 3 [L5] - 1234567	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] 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] 1	other data	other data
6	S852741	PB	Business 4	Business 4	Business 4
7	S741963	ET	Business 5	Business 5	Business 5	Business 3 [L5]- 1234567;Business 4 [L6]- 45612	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] 1	other data	other data
8	S741852	GB	Business 5	Business 5	Business 5	siness 2 [L7](45678) - 45678;Business 5 [L7](96385) - 96385;Business 3 [L5]- 1234567;Business 4 [L6]- 45612	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;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] 1	other data	other data
Main

Testing.xlsm
	A	B		D
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

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]

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]

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.

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,