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

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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 NumberBusiness Name
Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678Business/Technology
Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 2a [L7](78945) - 78945Business/Technology/Operations
Business 2 [L7](96385) - 96385Business
Business 3 [L5] - 1234567Operations
Business 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 IDMS ID NameSectorBusiness 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 4sector2Technology
12345Business 1 [L6]sector1Business
45678Business 2 [L7]sector2Technology
78945Business 2a [L7]sector3Operations
96385Business 2 [L7]sector1Business
1234567Business 3sector3Operations
45612Business 4sector2Technology


Let me know if more information is needed.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
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.

VBA 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 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,
 
Upvote 0
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.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Account 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 Name
2S123456GBBusiness 1Business 1Business 1Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678other dataother dataother data
3S456789ETBusiness 2Business 2Business 2Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 2a [L7](78945) - 78945other dataother dataother data
4S789123IGBusiness 3Business 3Business 3Business 2 [L7](96385) - 96385other dataother dataother data
5S456123PBBusiness 4Business 4Business 4Business 3 [L5] - 1234567other dataother dataother data
6S741852GBBusiness 5Business 5Business 5Business 3 - 1234567;Business 4 - 45612other dataother dataother data
Main


Key Sheet:
Testing.xlsm
ABCD
1ID #ID NameAreaBusiness Name
2(12345)Business 1 [L6]GBBusiness
3(45678)Business 2 [L7]ETTechnology
4(78945)Business 2a [L7]IGOperations
5(96385)Business 2 [L7]GBBusiness
6(1234567)Business 3 [L5]PBOperations
7(45612)Business 4 [L6]ETTechnology
812345Business 1 [L6]GBBusiness
945678Business 2 [L7]ETTechnology
1078945Business 2a [L7]IGOperations
1196385Business 2 [L7]GBBusiness
121234567Business 3 [L5]PBOperations
1345612Business 4 [L6]ETTechnology
Key



Updated Code:
VBA 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,
 
Upvote 0
Anyone able to provide any guidance to the problem I am having? I cant seem to figure out what I am missing.
 
Upvote 0
MHamid said:
I would prefer a VBA code,
Click to expand...
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).
 
Upvote 0
Hi Peter,

Peter_SSs said:
If that is the case, would it be acceptable for your Windows operating system only?
Click to expand...
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).
Click to expand...
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,
 
Upvote 0
MHamid said:
My preference would be to have the MS IDs listed once (without the parenthesis/brackets).
Click to expand...
Great, that suits my idea.

MHamid said:
Not sure what you mean here,
Click to expand...
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.xlsm
ABCD
1MS IDMS ID NameSectorBusiness Name
212345Business 1 [L6]sector1Business
345678Business 2 [L7]sector2Technology
478945Business 2a [L7]sector3Operations
596385Business 2 [L7]sector1Business
61234567Business 3sector3Operations
745612Business 4sector2Technology
Key


VBA Code: 
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.xlsm
AB
1Managed Segment (Home)-Node NumberBusiness Name
2Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678Business/Technology
3Business 1 [L6](12345) - 12345;Business 2 [L7](45678) - 45678;Business 2a [L7](78945) - 78945Business/Technology/Operations
4Business 2 [L7](96385) - 96385Business
5Business x - 99999
6Business 3 [L5] - 1234567;Business 2 [L7](11111) - 11111;Business 2a [L7](78945) - 78945Operations/Operations
7Business 3 - 1234567;Business 4 - 45612Operations/Technology
Main
 
Upvote 0
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.
VBA Code: 
d(CStr(a(i, 1))) = a(i, 4)

Updated code to the below in an attempt to use the MS ID Name
VBA Code: 
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,
 
Upvote 0
MHamid said:
I am getting duplicates, but I think it's ok.
Click to expand...
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
Click to expand...
I can see some issues with that if the sample data above is at all realistic. For example
  1. 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'?

  2. 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?
 
Upvote 0
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.
Click to expand...
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'?
Click to expand...
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.
Click to expand...
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.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Account 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 Name
2S123456GBBusiness 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/Technology
3S456789ETBusiness 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/Operations
4S789123IGBusiness 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 dataGBBusiness
5S456123PBBusiness 4Business 4Business 4Business 3 [L5] - 1234567Business 3 [L5] 1234567 -- Business 3a [L4] ### -- Business 3b [L3] ### -- Business 3c [L2] -- Business 3d [L1]other dataother dataPBOperations
6S852741PBBusiness 4Business 4Business 4
7S741963ETBusiness 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/Technology
8S741852GBBusiness 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/Operations
Main


The below sample of the Key, the focus is in column B.
Testing.xlsm
ABCD
1ID #ID NameAreaBusiness Name
212345Business 1 [L6]GBBusiness
345678Business 2 [L7]ETTechnology
478945Business 6 [L5]IGOperations
596385Business 5 [L7]GBBusiness
61234567Business 3 [L5]PBOperations
745612Business 4 [L6]ETTechnology
Key



Please let me know if I need to further explain.

Thank you,
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Search/Filter a Spreadsheet by User Entered Value
Replies
3
Views
139
jbennett01
J
M
  • Question Question
VBA Code - Run Code if cell value is not blank
Replies
0
Views
624
MHamid
M
M
  • Question Question
VBA Code - If statement to find exact match between columns as well as if value exists in another column
Replies
0
Views
935
MHamid
M
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
89
Fluff
Fluff
M
  • Question Question
Macro to lookup data within multiple columns
Replies
9
Views
601
MHamid
M

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top