search column A for keywords and put different keyword to cell to the right if found

  • Hey all,


    I apologize as I am very new to excel but hoping someone may have already written a scrip or macro or something that can accomplish what I'm trying to do. I have a spreadsheet and in 1 column there is a sentence in each cell. I'm basically wanting a way to search each cell in that column for a specific keyword and if found put a different keyword in the cell to the right. For example, it would search a cell for "drama, comedy, action, etc" and if found it would put the keyword "Movie" in the cell to the right. It would also search for keywords "show, sitcom, etc" and if found put "TV show" in the cell to the right. If none were found it would put "Not categorized" in the cell to the right. If it found keyword drama & sitcom it would default to the first set of keywords and put "Movie" in the cell to the right. Does this make sense? Is there a simple way to accomplish this? Please let me know if additional information is needed.


    Thank you!

  • Hi and Welcome to the Forum :smile:


    As long as you have already established your Reference List ... a formula can handle your request ...


    Best thing to do is to attach a sample file ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hey thanks for the reply! Here is a sample I created real quick.


    So I guess what I'm needing is a formula? I have a list of all my movies, tv shows and music and most of them have specific keywords in them that I can use to differentiate between movie, music, tv show. For example, the movies have the genre in them like comedy, drama, action, etc. So based of these keywords I'm wanting to put the word "Movie" in the cell to the right. Same for TV Shows. They will say like sitcom or series so want to put the word "TV Shows" in teh cell to the right. Can anyone help with a formula to do this? Once I have the basic formula down I can probably figure out how to tweak it with different keywords etc that I find. Thanks!

  • Thanks for your sample file ...


    As I said in the previous message ... you must have a Reference List ... !!!


    Please update your sample file ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Carim,


    Forgive my ignorance. Very new to excel. What is a "reference list"?


    Oh I do see you put something together for me in your latest post! Thanks! Trying to understand how to set up the reference list correctly now. Can I add lets say all keywords for in a single cell? So for Movies have something like "Comedy, Drama, Action, Horror, etc." Or do I have to put every keyword on a separate row?


    Thanks again Carim!

  • Now you do have a draft of your ' compulsory ' Reference List ...


    Each keyword has to go in a single cell ... so you do need to fill in both Columns ...


    Then you will only need to adjust the formula in Sheet 1 accordingly ...


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Carim, your formula fails when there are 2 keywords in a column A cell. For example B7 returns "Movie" not "TV Show", only the first keyword is picked up.


    I think the formula needed to account for that would be rather complex so maybe a UDF would be better. Note I used an Excel built-in Table for the reference list.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • [USER="33159"]KjBox[/USER]


    The Reference list is a quick mock-up designed to explain the need for a List ...


    It has nothing to do with the real list the OP has not yet built ....:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thank you for the replies. So, the actual excel list i'm working is work related with some sensitive data in it and that is why I cannot paste it here so I just used the movie, music & tv shows as an example hoping I can get a simple formula for what I'm trying to do. I have a list of ticket titles that may contain certain keywords such as "trips, setup, odometer, login, Grades". these titles are all in column L. I'm wanting to look for the keywords in column L and if it finds one put it in column Q. This is all in Sheet1. I created a reference list on Sheet3 like Carim's example and pasted this formula in column Q but the results return #N/A. Not sure what I'm doing wrong.


    Here is the formula I put in Column Q:


    =INDEX(Sheet3!$B$1:$B$8,MATCH(TRUE,ISNUMBER(SEARCH(Sheet3!$A$1:$A$8, L3)),0))


    I take it the "L3" would be the cell that it is searching on Sheet1 for the keywords on sheet3 column A and if it finds one it would paste the corresponding Sheet3 column B word in that field where I pasted the formula, is that correct?


    Sorry if I'm way off base on this. There are thousands of rows so really want to automate this ;)


    KjBox - I really like how this is set up. Not sure what a UDF is but this seems a lot cleaner looking


    I will put together my reference list and attach it here once completed. Question on the reference list though. Is it case sensitive? If I put "trips" will it still pick up "Trips"? What about the word Setup or Login if it shows as Set Up or Log in? Do I need to create separate rows for that?


    Thanks again for all the help!

  • Ok here is a better representation of what I'm trying to accomplish. It has the reference list on Sheet2 and some examples on Sheet1. Keep in mind the actual excel sheet i'm working the information will be in different columns. It will actually be Column D on Sheet 1 that will need to be searched for keyword


    Thanks!

  • Quote

    Once I have the basic formula down I can probably figure out how to tweak it with different keywords etc that I find. Thanks!


    Seems that your ' actual ' sample file is NOT RELATED AT ALL with your initial movies' List ....


    In Sheet 1 ... you are storing sentences and comments ... and your Sheet 2 is holding a list which is OBVIOUSLY incomplete (...by design ....)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Right, I was using the movies list as an example as I was just trying to figure out and understand the basics of doing something like this. And then was hopefully going to tailor it to my needs. My sheet2 has the majority of keywords I would need to search for but I would also like the ability to be able to add additional keywords if I start coming across tickets that have a different keyword in the title of them. The titles will typically contain a sentence with policy numbers and some other customer information but should also give a short word or 2 regarding the issue. It's that short word or 2 in the title that I'm trying to pull the keyword from. Sorry to give you the run around as I thought there would just be a simple way or formula to do this without getting too detailed into the specifics.


    Thanks!