Finding a text string in a string of text, then returning cell related data

[erollman]

Hi folks,
I have a problem that has me stumped sideways til next Thursday. Maybe I am just fried because it's Friday and I have been struggling with this all week, or maybe I am still not seeing the bigger picture. This is why I come to ask help from the experts.

Problem:
Searching a string of text in a column to find a specific string of text

Criteria:
Need to be able to identify which cell the particular string of text is found in(if any), and return data from a cell elsewhere in the document, but on the same row.

Example:


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]OEM Number
[/TD]
[TD]Search Keywords
[/TD]
[TD]Data to be returned
[/TD]
[/TR]
[TR]
[TD]11604
[/TD]
[TD]160033, 16-1000, 396-703
[/TD]
[TD]Jerry
[/TD]
[/TR]
[TR]
[TD]160033
[/TD]
[TD]11605, 11662, 113-275, 11604
[/TD]
[TD]Mack
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Basically I need the formula to be able to find 11604 or 160033 in colum "Search Keywords" and return data from a cell related on the same row as 11604 or 160033, ie. Jerry or Mack. I am sure there is an easy way of doing this, but I am stumped. It's gotta be the impending weight I'll gain from Turkey day next week.

Any help would be greatly appreciated.


erollman

 

[erollman]

Hi erollman. Assuming your data is on columns A to C, I'm not sure if you want to find each value of column A in anywhere in column B or just in the same as row the searched value. Try this macro and then we can go from there if it's not what you want. The returned value is copied to Sheet2.

Sub test()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim bottomB As Integer
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim rng1 As Range
    Dim rng2 As Range
    For Each rng1 In Range("A2:A" & bottomA)
        For Each rng2 In Range("B2:b" & bottomB)
            If rng2 Like "*" & rng2 & "*" Then
                rng2.Offset(0, 1).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next rng2
    Next rng1
    Application.ScreenUpdating = True
End Sub

Hey mumps,
VBA code is something I am extremely virgin to. I have only been able to get a couple to ever run in my day. One is a pretty cool highlight code for a moving cell, and the other is for a database search. Would it be too much trouble to ask for instructions on how to insert/run this? I have my information setup in columns A, B, C Just as in the sample submitted in my question. I right clicked the tab and hit view code after saving the workbook as an macro enabled workbook. I pasted your code onto the VBA editor, hit save, and closed, but nothing seemed to work or show up on sheet 1 or 2. Where did I go wrong?

 

[erollman]

Hi erollman,

I currently have only a Dutch version of Excel at hand, so I'm not able to translate all functions to their English counterpart.
But I'll try to give you an idea.
Assume in your example that the data is in the columns A:C.
If so you could try setting up a formula to try to match both values and when found one, returns the value from column C:

    X1=IF(OR(find_in_str<find_in_str>(B1,"11604"),find_in_str<find_in_str>(B1,"160033")),C1,"")

You need to replace 'find_in_str<find_in_str>' with the correct English Excel function.

I hope that, despite not be able to give full working solution, it will help you.

Paul</find_in_str></find_in_str></find_in_str>

Hi Paul,
I wrote my formula just as you did with my information in colums A through C, just as in the example, but it gives an error saying that where you C1 is there should be a logical function as part of the OR function? Any suggestions?

 

[Aladin Akyurek]

Hi Aladin,
Sorry, but I am unfamiliar with the F2, control+shift+enter, and copy down that you are mentioning. What does it do? It seems you have been able to make the data work the way I need it to, but I am uncertain of what you are asking. Is the lookup supposed to include 9.9999999999+307, or is that a pasting error?

Please forgive me for my inexperience, but thank you for your time and consideration with my question.

That big number is used as a look up value by the LOOKUP function...

See:
https://dl.dropboxusercontent.com/u/65698317/aaLookupSearch erollman.xlsx

 

[erollman]

That big number is used as a look up value by the LOOKUP function...

See:
https://dl.dropboxusercontent.com/u/65698317/aaLookupSearch%20erollman.xlsx

Hi Aladin,
You are infinitely more experience with excel than I am obviously. I cannot get my table to work the way that you have yours. Your table is working to do exactly what I need, but I cannot duplicate it on mine. I tried to copy your formula, no luck. I tried to rekey your formula, no luck. I am sure there is a trick to it I am not seeing. Thank you for sending me the link to the dropbox file. Unfortunately it only frustrates me more that I can't duplicate it. It keeps giving me a circular reference error. I hit OK and the result is 0.

 

[markmzz]

Another way:

Layout

[TABLE="width: 284"]
<colgroup><col width="154" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5632;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <tbody>[TR]
[TD="class: xl63, width: 154, bgcolor: transparent"]Search Keywords[/TD]
[TD="class: xl63, width: 101, bgcolor: transparent"]Data to be returned[/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 68, bgcolor: transparent"]OEM Number[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"]Result[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]160031, 16-1000, 396-703[/TD]
[TD="class: xl63, bgcolor: transparent"]Jerry[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11604[/TD]
[TD="class: xl65, bgcolor: yellow"]Mack[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]160033, 16-1001, 396-704[/TD]
[TD="class: xl63, bgcolor: transparent"]Test[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]160033[/TD]
[TD="class: xl65, bgcolor: yellow"]Test[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]11605, 11662, 113-275, 11604[/TD]
[TD="class: xl63, bgcolor: transparent"]Mack[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]396-703[/TD]
[TD="class: xl65, bgcolor: yellow"]Jerry[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]*************************[/TD]
[TD="class: xl63, bgcolor: transparent"]******************[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]************[/TD]
[TD="class: xl63, bgcolor: transparent"]******[/TD]
[/TR]
</tbody>[/TABLE]

Formula

In E2 - use only Enter to enter the formula

=LOOKUP(1,1/(LEN(", "&$A$2:$A$4&",")-LEN(SUBSTITUTE(", "&$A$2:$A$4&",",", "&$D2&",",""))),$B$2:$B$4)

Or

=LOOKUP(1,1/(LEN(","&SUBSTITUTE($A$2:$A$4," ","")&",")-LEN(SUBSTITUTE(","&SUBSTITUTE($A$2:$A$4," ","")&",",","&$D2&",",""))),$B$2:$B$4)

Markmzz

 

[DeBeuz]

Hi Paul,
I wrote my formula just as you did with my information in colums A through C, just as in the example, but it gives an error saying that where you C1 is there should be a logical function as part of the OR function? Any suggestions?

Oops, my mistake
I expect the formula to return a position.
So after the closing braces ")" of each find_in_str (FIND?) you should place the logical expression ">0".

Succes,

Paul

 

[mumps]

Hi erollman. The macro must be placed in a regular module. Hold down the ALT key and press the F 11 key to open the Visual Basic Editor. On the top menu click 'Insert' and then click 'Module'. Copy and paste the macro in the empty code module that opens up. Press the F5 key to run the macro. Close the module window to return to you sheet. Please let me know how it works out.

 

[erollman]

Another way:

Layout

[TABLE="width: 284"]
<tbody>[TR]
[TD="class: xl63, width: 154, bgcolor: transparent"]Search Keywords
[/TD]
[TD="class: xl63, width: 101, bgcolor: transparent"]Data to be returned
[/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 68, bgcolor: transparent"]OEM Number
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"]Result
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]160031, 16-1000, 396-703
[/TD]
[TD="class: xl63, bgcolor: transparent"]Jerry
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11604
[/TD]
[TD="class: xl65, bgcolor: yellow"]Mack
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]160033, 16-1001, 396-704
[/TD]
[TD="class: xl63, bgcolor: transparent"]Test
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]160033
[/TD]
[TD="class: xl65, bgcolor: yellow"]Test
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]11605, 11662, 113-275, 11604
[/TD]
[TD="class: xl63, bgcolor: transparent"]Mack
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"]396-703
[/TD]
[TD="class: xl65, bgcolor: yellow"]Jerry
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]*************************
[/TD]
[TD="class: xl63, bgcolor: transparent"]******************
[/TD]
[TD="class: xl63, bgcolor: transparent"]**
[/TD]
[TD="class: xl63, bgcolor: transparent"]************
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[/TR]
</tbody>[/TABLE]

Formula

In E2 - use only Enter to enter the formula

=LOOKUP(1,1/(LEN(", "&$A$2:$A$4&",")-LEN(SUBSTITUTE(", "&$A$2:$A$4&",",", "&$D2&",",""))),$B$2:$B$4)

Or

=LOOKUP(1,1/(LEN(","&SUBSTITUTE($A$2:$A$4," ","")&",")-LEN(SUBSTITUTE(","&SUBSTITUTE($A$2:$A$4," ","")&",",","&$D2&",",""))),$B$2:$B$4)

Markmzz

Very Nice Mark. Worked perfectly. I can do the rest from here. You are a lifesaver.

 

[erollman]

Thanks to all of you have have taken your time to lend me a hand. I appreciate it greatly. I will be able to now use the data given to help me reduce my work load and accomplish more projects in the near future. Thanks again to you all, and to the people running the Mr. Excel Forum. This is an excellent tool.

 

[markmzz]

Very Nice Mark. Worked perfectly. I can do the rest from here. You are a lifesaver.

You're welcome and thanks for the feedback.

Markmzz