View Full Version : [SOLVED:] Extract Word into Next Row From DataSet - From Searching WordList In Another Sheet
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.
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
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:)
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:)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.