PDA

View Full Version : [SOLVED] Extract Word into Next Row From DataSet - From Searching WordList In Another Sheet



dj44
11-05-2016, 05:30 AM
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


17518


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
11-05-2016, 06:06 AM
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
11-05-2016, 06:23 AM
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
11-05-2016, 06:26 AM
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
11-05-2016, 06:40 AM
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:grinhalo:

Thank you so much and kudos to you

Have a great weekend folks:)