1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Need help filtering keywords in excel

Discussion in 'White Hat SEO' started by lolseo, Nov 7, 2013.

  1. lolseo

    lolseo Power Member

    Joined:
    Feb 8, 2013
    Messages:
    702
    Likes Received:
    26
    Hi guys,

    Ive got a list of 200+ keywords from the keyword planner.

    Im wanting to break these into categories for example.

    (everything below is a fictional example)

    My main keyword was "dog grooming"

    Example keywords are
    "easy dog grooming tips"
    "how to make money with dog grooming"
    "how to walk your dog"
    "dog grooming for beginners"
    "dog behaving badly"

    Does anyone have a solution so i can "ctrl + f" search for "dog grooming" and copy the entire rows (not just the cells)

    I'd like to run a search in excel for "dog grooming" and copy all rows (so the search volume, cpc etc associated with the term) into its own sheet. I've followed a vlookup tutorial but i don't think vlookup is what i need for this scenario.

    The way ive filtered in the past is coming up with a set of "groups" for example

    "Dog walking" - 1
    "Dog Grooming" - 2
    "Dog Training" - 3

    I'd then go down my list and mark a number next to each phrase
    "easy dog grooming tips" - 2
    "how to make money with dog grooming" - 2
    "how to walk your dog"
    "dog grooming for beginners" -2
    "dog behaving badly"

    I'd then filter that column for the number and copy and paste everything onto a separate sheet.

    This is very time consuming however.

    Does anyone have a solution so i can "ctrl + f" search for "dog grooming" and copy the entire rows (not just the cells)
     
  2. Schvamp

    Schvamp Power Member

    Joined:
    Feb 13, 2012
    Messages:
    684
    Likes Received:
    549
    Location:
    Hogwarts
    I can create you a tool for you wich you can use for upcoming projects aswell.
    PM me your skype, no payments needed :)
     
  3. lolseo

    lolseo Power Member

    Joined:
    Feb 8, 2013
    Messages:
    702
    Likes Received:
    26
    Im sure there will be a method using excel for this?
     
  4. DenisWeb

    DenisWeb Registered Member

    Joined:
    Aug 9, 2012
    Messages:
    67
    Likes Received:
    12
    Use filters. Set custom text filter with your KW on "keywords column" and you will see phrases only with your KW
    filter.JPG
     
    Last edited: Nov 8, 2013
  5. lolseo

    lolseo Power Member

    Joined:
    Feb 8, 2013
    Messages:
    702
    Likes Received:
    26
    Thanks for the reply Denis but this doesn't work.

    Using the filter option shows, for example all 200 of my keywords. The term "dog grooming" may be nested as the 3rd and 4th term of my search. Even using this option, its essentially the same as me going down the list by hand numbering it.
     
  6. ooPekoPeko

    ooPekoPeko Newbie

    Joined:
    Sep 5, 2013
    Messages:
    16
    Likes Received:
    16
    CTRL+H (there must be something like Find and Replace, sorry don't have excel at hand right now)--> "*dog grooming*" (put "*" before and after keywords to select all the cell) --> search all --> then select all results in results bar --> click on excel window --> CTRL+C --> Select empty row or another sheet and CTRL+V
     
    Last edited: Nov 8, 2013
  7. lolseo

    lolseo Power Member

    Joined:
    Feb 8, 2013
    Messages:
    702
    Likes Received:
    26
    Thanks for the reply Peko, but this is what i've already done.

    My columins are for example

    Keyword - search volume - cpc

    Using your method (the same as mine, apart from using the "*" before and after the keyword only allows me to copy that cell, containing the keyword. I need to copy all the columns (the entire row for all instances of terms containing "dog grooming", NOT just the cell containing my keyword.

    When doing ctrl+f and highlighting everything in the result section, it only highlights the individuals cells, not the entire row.
     
  8. lolseo

    lolseo Power Member

    Joined:
    Feb 8, 2013
    Messages:
    702
    Likes Received:
    26
    Anyone else have any advice?
     
  9. Guifi

    Guifi Newbie

    Joined:
    Oct 6, 2013
    Messages:
    2
    Likes Received:
    0
    This might not be perfect solution, but you might first categorize your keywords with some =SEARCH and conditionals, and then just filter out each category. Or automate a lil bit more with vlookup.

    Here is a handy reference on slicing and dicing strings off text specially lesson2

    Google this, I can't post links yet since I'm new here =D
    Microsoft excel for SEOs - Distilled


    Hope it helps!
     
  10. devde

    devde Registered Member

    Joined:
    Jan 5, 2013
    Messages:
    76
    Likes Received:
    23
    create an additional column and put this code (assuming A is the column where you have your keywords)

    Code:
    =IF(ISERROR(FIND("dog grooming",A1,1))=1,0,A1)
    If the keyword contains "dog grooming" the formula will return the keyword itself otherwise it will return 0.
    You can then sort the spreadsheet, you will have all the 0 at top or bottom (depending if you choosed ascending or descending sort), delete all the rows which contain 0 and your end result will be the rows containing only the keyword "dog grooming"
     
  11. lolseo

    lolseo Power Member

    Joined:
    Feb 8, 2013
    Messages:
    702
    Likes Received:
    26
    Thanks ill give this ago