Search specific text and past in specified cell

[acc16]

Hi,

Any help is appreciated on this query.

I have a sentence in column C (Sheet1) and I need to search a specific text which is placed in Sheet2 Column A1 till A100. I am trying to check if Sheet1 Column C has the text from Sheet2 Column A and place that text in Sheet1 Column E.

For example.
Sheet1-Column C2 = "Error in P0001 as on specific date"
Sheet1-Column C3 = "Not able to find 0003 in field SD"
Sheet1-Column C4 = "Not alligned to P0003 for the record".

Sheet2-Column A1 = "0000"
Sheet2-Column A2 = "0001"
Sheet2-Column A3 = "0002"
Sheet2-Column A4 = "0003"

My requirement is to read Sheet2 A:A and then find/search if that exists in Sheet1 Column C:C and if it exists then paste that value ("0000"/"0003") in corrosponding column "E".

 

[Jadegirl]

That is awesome... but is there a formula that can be used vice the VBA editor?

 

[hiker95]

acc16,

Be back later with a faster solution.

 

[Jadegirl]

cool look fwd. to your reply.

 

[hiker95]

acc16,

Same screenshots as my last reply, but the following macro is a lot faster than the previous 2 macros (working with arrays in memory is a lot faster than working in a worksheet).


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub SearchForStringV4()
' hiker95, 08/06/2011
' http://www.mrexcel.com/forum/showthread.php?t=569493
Dim w1 As Worksheet, w2 As Worksheet
Dim S, C, E() As String, ss As Long, cc As Long
Dim H As String
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
S = w2.Range("A1:A100")
C = w1.Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row)
ReDim E(1 To UBound(C), 1 To 1)
For cc = LBound(C) To UBound(C)
  H = ""
  For ss = LBound(S) To UBound(S)
    If S(ss, 1) <> "" Then
      If InStr(C(cc, 1), S(ss, 1)) > 0 Then
        If E(cc, 1) = "" Then
          E(cc, 1) = S(ss, 1)
        Else
          H = E(cc, 1)
          H = H & "|" & S(ss, 1)
          E(cc, 1) = H
        End If
      End If
    End If
  Next ss
Next cc
w1.Range("E2").Resize(UBound(E)) = E
w1.Columns(5).AutoFit
w1.Activate
End Sub

Then run the SearchForStringV4 macro.

 

[Jadegirl]

HelloHiker95,
Is there a formula to do this? I'd love to learn.
Thanks!

 

[hiker95]

Jadegirl,

There are some formulae Guru's out there.

You may want to start your own New Post, and in the New Post explain what your are attempting to do with formulae.

You could also in your own New Post have a link to this post by acc16, as a reference.


In your New Post you probably should have screenshots of your raw data, and the outcome you are looking for:

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[acc16]

Perfect!!

Thanks a lot hiker95...