Extract Word into Next Row From DataSet - From Searching WordList In Another Sheet

[dj44]

Hi folks,

Hope all are great today.
I have encountered a issue that I don’t know how to explian
So I'll try here

Sorry title is wrong should be NEXT COLUMN


dataextract.jpg


After Something happens a macro maybe?
The Data sheet Becomes as above.

So I need some ideas on how to proceed on this task
How do I extract the word from a column from another worksheet list

Your advice greatly appreciated as I never done this before and cant find anything that describes what this is extracting data from a worksheet to a row adjacent?

Thank you

[Kenneth Hobs]

What would "Main Word" be if "Data" is "Pear and oranges and apple are in the data column."
1. Apple
2. Pear
3. Apple Oranges Pear
4. Pear oranges apple
5. etc.

[mana]

Option Explicit


Sub test()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
     Dim k As Long
     Dim j As Long
     Dim s As String
     Dim tmp As String
     
     Set ws1 = Worksheets("Sheet1")
     Set ws2 = Worksheets("Sheet2")
     
     For j = 2 To ws1.Cells(Rows.Count, "D").End(xlUp).Row
        tmp = ""
         For k = 2 To ws2.Cells(Rows.Count, "A").End(xlUp).Row
             s = ws2.Cells(k, "A").Value
             If ws1.Cells(j, "D").Value Like "*" & s & "*" Then
                tmp = tmp & "," & s
             End If
         Next
         ws1.Cells(j, "C").Value = Mid(tmp, 2)
     Next
     
End Sub

[dj44]

Hello Kenneth,
oh yes well spotted that flaw there, well it's very forgiving the data,
I'm just trying to find a quick and easy way to get an idea of what each row data pertains to without having to read the paragraphs of data

Main Word

Apple

Oranges

Pear

so in this case

it would search for the first word it finds in the column data


However

if it would be able to search for strings - that would be even more awesome so I suppose I could have

Main Word

Apple Apple Oranges

Oranges Oranges Pear Oranges Apple

Pear

As aexcel data novice as well, I'm not sure what the right terminology is for such a thing search string within strings


thank you

[dj44]

Hello Mana,


Thank you ever so much it took me half an hour to try and even formulate the idea as I had so many wrong thoughts of what I was trying to do but this worked beautifully.

It worked on strings too.

So you saved me a good weekend of searchign threads for ideas and testing alot of dud dead ends as always happens with me

Thank you so much and kudos to you

Have a great weekend folks