Find word in a column from list of words and report word found [Closed]

Report
Posts
3
Registration date
Wednesday November 22, 2017
Status
Member
Last seen
November 23, 2017
-
Posts
3
Registration date
Wednesday November 22, 2017
Status
Member
Last seen
November 23, 2017
-
Hello,

I have a large data set that I would like to search one column that contains sentences or purchases. I'd like to search from a group of words and have the word reported in an adjacent column. For example (see below), if the column contains flowers and supplies purchased can I search that column with a list of different flower names (i.e. begonia, sunflower, dandelion, lilies, roses, etc.) and whatever is found get that name reported in the next column. I don't have much background in this and I know it would need to be a part search and not a whole word search, but all I have seen are reports with True or False.

purchases results
25 red begonia begonia
3 bags of soil
5 yellow sunflower sunflower
2 flower pots
25 purple begonia begonia

Thanks in advance for the help!


2 replies


I have some qualifying questions, please if I may?
Does the cell contain:
"25 red begonia begonia"

Or, does it contain:
A1=25
A2=red
A3=begonia
A4=begonia

This data set that you are referring to, would be much easier with a database, and a report! Just saying!
Posts
3
Registration date
Wednesday November 22, 2017
Status
Member
Last seen
November 23, 2017

The layout is D2="25 red begonia", D3="3 bags of soil", D4="5 yellow sunflower" and I would like the result to be in H2="begonia", H3="", H4="sunflower".

This is my first time using this, but I am not allowed to post the original document so I made one with the same layout but excluded the information that is not needed. The P column contains the list of flower names I'd like to use for the search. How do I upload excel document?
Blocked Profile
You do not upload the document. You upload a screenshot of the layout! There is a picture upload button.

Just to make certain, you want us to write code that will delimit the cell input, and place the delimited result in another cell?
Posts
3
Registration date
Wednesday November 22, 2017
Status
Member
Last seen
November 23, 2017

No I know how to delimit. What I would like to do is search the purchases column using the keywords from the "List of Flowers". I have this code already but I didn't know if this was the right way to go about it since it was searching a column that only had this word in it. Also it places the answers in the E column where I would need it to be placed in the H column. I wanted to get other opinions on a better/easier way if possible.

Sub findflowers()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")

For i = 1 To ws.Range("D65536").End(xlUp).Row
j = InStr(ws.Cells(i, 6).Value, "african lily")
If j <> 0 Then ws.Cells(i, 7).Value = "african lily"
j = InStr(ws.Cells(i, 6).Value, "alpine thistle")
If j <> 0 Then ws.Cells(i, 7).Value = "alpine thistle"
j = InStr(ws.Cells(i, 6).Value, "amazon lily")
If j <> 0 Then ws.Cells(i, 7).Value = "amazon lily"
j = InStr(ws.Cells(i, 6).Value, "baby's breath")
If j <> 0 Then ws.Cells(i, 7).Value = "baby's breath"
Next
End Sub

But this would be a lot of lines because I have a list of about 150 terms I'd like to search. And this code was if the only word in the cell were the names of the flowers and they aren't since there are amounts and colors in the same column.


Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!