+ Reply to Thread
Results 1 to 7 of 7

Copying Keywords from Matching List Into New Sheet

  1. #1
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Copying Keywords from Matching List Into New Sheet

    Hello,

    I'm running into a issue where I have a list of keywords and need them to match the list of keywords against a list if those keywords match those on the list then copy those keywords into another spreadsheet. I tired using the advanced filter, vlookup, and a match function all methods returned Null or errors. I enclosed an example of this problem to help clarify any information I may be not stating correctly. Any help would be appreciated...


    Thank You
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Copying Keywords from Matching List Into New Sheet

    Hello,

    what is the bigger picture here?

    Why do you want to copy the keyword "program" itself into Sheet2? What would that achieve? You'd end up with a range of cells that all contain the word "Program". Then what? Do you want to count them and work out how many times the keyword "program" appears in the entries in column B on sheet 1?? If so, there's an easy Countif formula that can be used. Put this into cell N2 on Sheet1

    =COUNTIF(B:B,"*"&M2&"*")

    and copy down. Or, put this into cell C2 on Sheet 2 and copy to the right

    =COUNTIF(Sheet1!$B:$B,"*"&Sheet2!C1&"*")

    If I've completely misunderstood what you're trying to do, please explain the result you want to achieve.

    cheers, teylyn

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Copying Keywords from Matching List Into New Sheet

    Second thoughts. Maybe you want the text in column B to end up in Sheet2? You use "keyword" in a very ambiguous way.

    Try this: On Sheet2, write the words program accredited job best year into cells F1 to J1. Then use this formula in cell F2

    =IF(ISNUMBER(SEARCH(F$1,$B2)),ROW(),"")

    Copy across to J2 and then copy down to row 2002.

    On sheet 2, use this formula in cell C2

    =IFERROR(INDEX(Sheet1!$B:$B,SMALL(Sheet1!F:F,ROW(A1))),"")

    Copy across to cell G2, then copy down about 300 rows until no more text appears as a result of the formula.

    See attached file.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Re: Copying Keywords from Matching List Into New Sheet

    Hi teylyn,

    Thank you for the quick reply. This solution worked perfectly!! Also to answer your first question the objective was to find a means to find all keywords matching against a list and place the ones that match into sheet 2. I do a lot of keyword research with thousands of rows of keyword data by creating list of "valued" keywords to search for I was hoping to just use that list i.e (best, jobs, etc) all copy any keywords match those into a new sheet (I hope I didn't make that confusing...) nonetheless your solution worked like a charm!

    Thank you again!

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Copying Keywords from Matching List Into New Sheet

    Thanks for the feedback, but you still have not clarified your initial goal. Which of my two answers provided it?

    I know it can be hard to explain things but you used "keyword" for everything and it was not clear whatsoever if you wanted the list keyword or the column B keyword in the results. I posted an approach for counting the occurrences of the list keyword, and another approach for listing the column B keywords in sheet 2.

    Now you say "Thanks, that worked", but I have no clue which approach worked for you. You never reacted to my questions for clarification. I'm happy that something I did apparently solved your issue, but it's still frustrating to not know which interpretation of your problem I got wrong.

  6. #6
    Registered User
    Join Date
    08-11-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    78

    Re: Copying Keywords from Matching List Into New Sheet

    Hi Teylyn,

    Sorry for the foggy explanation (you didn't get anything wrong) I should have been clearer and probably renamed "keyword" to "Researched Words." The second approach in which you were able to place all of the keywords from column B into sheet 2 was the one that worked great...By using that I was able to have all the keywords containing programs into a list, and all keywords containing jobs into another list, etc...

    Thank you again

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Copying Keywords from Matching List Into New Sheet

    all good, thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] highlight keywords in a list when they occur in a string on another sheet
    By 180drop in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-16-2015, 05:17 PM
  2. [SOLVED] List of keywords to search from another sheet cell contains many other letters
    By wanian7 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-18-2015, 10:48 PM
  3. Find multiple keywords in all columns and copying discovered rows into new sheet
    By noxfinitie in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 07-03-2014, 01:14 PM
  4. [SOLVED] Search for keywords and copy rows containing keywords to new sheet
    By lenorsk in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-15-2013, 06:54 AM
  5. [SOLVED] How to search across 2 columns for keywords from a list and copy each row to a new sheet?
    By keithtran12 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 04-01-2013, 05:16 AM
  6. Matching lists of keywords
    By ccastell88 in forum Excel General
    Replies: 3
    Last Post: 11-11-2012, 08:27 AM
  7. Replies: 1
    Last Post: 04-23-2012, 04:23 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1