Jump to content

  • Log in with Facebook Log in with Twitter Log In with Google      Sign In   
  • Create Account

Subscribe to HRA Now!

 



Are you a Google Analytics enthusiast?

Share and download Custom Google Analytics Reports, dashboards and advanced segments--for FREE! 

 



 

 www.CustomReportSharing.com 

From the folks who brought you High Rankings!



Photo
- - - - -

Managing Keywords/ Phrases With Excel


  • Please log in to reply
13 replies to this topic

#1 jsp1983

jsp1983

    HR 3

  • Active Members
  • PipPipPip
  • 98 posts
  • Location:Blackpool, UK

Posted 16 March 2010 - 04:11 PM

Does anybody know if it's possible to take a selection of cells and 'sort' them by, say, a given word?

For example, say I've got 200 phrases, all relating to 'widgets', 'sprockets' and 'doohickeys', but aren't separated in to groups. Is there a way to tell Excel to arrange them in a column in such a way that 'sprockets'-related phrases are ordered first, followed by 'widgets' and 'doohickeys' etc?

#2 Jill

Jill

    Recovering SEO

  • Admin
  • 32,863 posts

Posted 16 March 2010 - 04:30 PM

Yes. Use the "sort" function.

#3 jsp1983

jsp1983

    HR 3

  • Active Members
  • PipPipPip
  • 98 posts
  • Location:Blackpool, UK

Posted 16 March 2010 - 06:42 PM

QUOTE(Jill @ Mar 16 2010, 09:30 PM) View Post
Yes. Use the "sort" function.


How? I can only seem to use it to sort by alphabetical order, rather than based on a set of words.

#4 chrishirst

chrishirst

    A not so moderate moderator.

  • Moderator
  • 6,670 posts
  • Location:Blackpool UK

Posted 17 March 2010 - 07:08 AM

write a macro/subroutine/function to do the sorting.

#5 Jill

Jill

    Recovering SEO

  • Admin
  • 32,863 posts

Posted 17 March 2010 - 10:30 AM

QUOTE(jsp1983 @ Mar 16 2010, 07:42 PM) View Post
How? I can only seem to use it to sort by alphabetical order, rather than based on a set of words.


It's in custom sort. You can filter certain keywords.

#6 qwerty

qwerty

    HR 10

  • Moderator
  • 8,573 posts
  • Location:Somerville, MA

Posted 17 March 2010 - 11:58 AM

If the keywords related to 'widgets', 'sprockets' and 'doohickeys' don't necessarily include the words 'widgets', 'sprockets' and 'doohickeys' what you could do is to add a new column. Call it Category or some such thing. Then go through each line, and associate the keyword phrase with the proper category: W, S, or D.

Once you've done that, you'll be able to select the whole worksheet and sort the data by category.

#7 Jill

Jill

    Recovering SEO

  • Admin
  • 32,863 posts

Posted 17 March 2010 - 12:49 PM

You may also want to consider multiple worksheets within one excel document.

#8 jsp1983

jsp1983

    HR 3

  • Active Members
  • PipPipPip
  • 98 posts
  • Location:Blackpool, UK

Posted 23 March 2010 - 10:50 AM

Well, I asked the same question on an Excel forum and seem to have a partial answer for it, which I thought I'd share:

http://www.mrexcel.c...747#post2254747

#9 jsp1983

jsp1983

    HR 3

  • Active Members
  • PipPipPip
  • 98 posts
  • Location:Blackpool, UK

Posted 01 April 2010 - 10:54 AM

I'm now trying to find a way of finding similar occurrences in my keyword phrases. It's not so bad when you only have a few to glance at, but when there are tens or hundreds of them, trying to find phrases contained in other phrases is seemingly impossible.

Does anybody have any ideas of how I can find these 'phrases within phrases' in Excel?

#10 marketsitepro

marketsitepro

    HR 2

  • Active Members
  • PipPip
  • 21 posts

Posted 01 April 2010 - 11:21 AM

Hi JSP,

Forget the sort A-Z feature. I do not know what version of excel you are using, but I would HIGHLY suggest 'Filter' button.

Paste all your keywords on row 2 and below. Then highlight the columns that you want to work with (to do this you click on the letter of the column). Then click the filter button (within the data tabs).

There will be a drop down menu for each column, in which you can sort/search. Choose 'text filters' and click on one of the options.

Since you are looking for 'phrases within phrases' then choose the 'contain' option. This means it will search and filter that specific column to which contain whatever phrase you want.

So for example: you have a long tail: People who like widgets and sprockets in NW California

well if you have thousands of these type of key phrases, then you can use the filter option to choose, let's say: widgets and sprockets

Then it will list every keyterm that include that PHRASE.

No formulas, no mess no fuss. Hopefully I understood your question and answered accordingly. Let me know...

#11 jsp1983

jsp1983

    HR 3

  • Active Members
  • PipPipPip
  • 98 posts
  • Location:Blackpool, UK

Posted 01 April 2010 - 11:47 AM

QUOTE(marketsitepro @ Apr 1 2010, 05:21 PM) View Post
Hi JSP,

Forget the sort A-Z feature. I do not know what version of excel you are using, but I would HIGHLY suggest 'Filter' button.

Paste all your keywords on row 2 and below. Then highlight the columns that you want to work with (to do this you click on the letter of the column). Then click the filter button (within the data tabs).

There will be a drop down menu for each column, in which you can sort/search. Choose 'text filters' and click on one of the options.

Since you are looking for 'phrases within phrases' then choose the 'contain' option. This means it will search and filter that specific column to which contain whatever phrase you want.

So for example: you have a long tail: People who like widgets and sprockets in NW California

well if you have thousands of these type of key phrases, then you can use the filter option to choose, let's say: widgets and sprockets

Then it will list every keyterm that include that PHRASE.

No formulas, no mess no fuss. Hopefully I understood your question and answered accordingly. Let me know...


Hi,

Thanks for replying.

The problem with that solution is that you have to know which phrases you want to look for - I don't necessarily know which phrases are already occurring in other phrases (I know some, but not all).

As I'm targetting a niche with longtail keywords, there are quite a handful of phrases that I'm trying to work with (several hundred so far) from the Keyword Tool.

In a way, I want to tell Excel "I don't know which phrases exist within other phrases, but I need you to find and highlight cells where this happens".

#12 Jill

Jill

    Recovering SEO

  • Admin
  • 32,863 posts

Posted 01 April 2010 - 11:55 AM

You would need a special keyword tool for that. Try checking some of the keyword tools on the market today as they do things like that.

And no, I don't want people to start listing them here.

#13 jsp1983

jsp1983

    HR 3

  • Active Members
  • PipPipPip
  • 98 posts
  • Location:Blackpool, UK

Posted 01 April 2010 - 03:11 PM

QUOTE(Jill @ Apr 1 2010, 05:55 PM) View Post
You would need a special keyword tool for that. Try checking some of the keyword tools on the market today as they do things like that.

And no, I don't want people to start listing them here.


Ah, fair enough.

A couple of the ones I've tried so far don't seem to have that functionality, unfortunately.

Anybody want to PM me some ideas of tools that might have this function? smile.gif

#14 marketsitepro

marketsitepro

    HR 2

  • Active Members
  • PipPip
  • 21 posts

Posted 01 April 2010 - 04:27 PM

Instead of trying to answer a question that I am still unclear about, let me try to clarify it. Let me give examples and you tell me if any come close

1) occurrence: You want pull and sort key phrases that show up more than once in your long tail key phrases. example 'doohickeys and widgets' might show up in 50 of your keyphrases, whereas 'widgets and sprockets' may show up in 30 of your keyphrases
2) combination: This is like calculus. example you have a 4 word key term, like: Doohicky Widgets and Sprockets. You can have up to (I'm guessing) 26 keyword combinations.
3) numbers: you want to break down all your keyword phrases by the amount of words in your keyphrase and sort them.
4) A combination of more than 1 of the above?

Do any of those work?

Since you do not know exactly what keyphrase then I guess you would have to define what a phrase is that would be pulled and sorted..




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

SPAM FREE FORUM!
 
If you are just registering to spam,
don't bother. You will be wasting your
time as your spam will never see the
light of day!