13 replies to this topic

[jsp1983]

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?

[Jill]

Yes. Use the "sort" function.

[jsp1983]

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.

[chrishirst]

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

[Jill]

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.

[qwerty]

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.

[Jill]

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

[jsp1983]

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

[jsp1983]

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?

[marketsitepro]

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...

[jsp1983]

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".

[Jill]

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.

[jsp1983]

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?

[marketsitepro]

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..