Help with Grouping Data

[loosefred]

Hi guys,

The excel sheet I'm working on is a report sheet which shows me the performance of each individual keyword in my client's paid search campaign. The client is a van hire company with 66 locations in the UK and what I'd like to do is to automatically group the keywords so they are associated with a particular location. Part of my sheet looks like this:

Keyword	Transactions	Revenue	Clicks	Impressions	Cost
+van +hire +aberdeen	x	x	x	x	x
+van +hire +coventry	x	x	x	x	x
+van +hire +nottingham	x	x	x	x	x
+van +hire +sheffield	x	x	x	x	x
+van +hire +milton +keynes	x	x	x	x	x
van hire cardiff	x	x	x	x	x
+van +hire +banbury	x	x	x	x	x

<tbody>
</tbody>

What I would like to do is to have a column to show which location the keyword is associated with, based on a list of the different branches, so that it would look like this:

Keyword	Branch	Transactions	Revenue	Clicks	Impressions	Cost
+van +hire +aberdeen	Aberdeen	x	x	x	x	x
+van +hire +coventry	Coventry	x	x	x	x	x
+van +hire +nottingham	Nottingham	x	x	x	x	x
+van +hire +sheffield	Sheffield	x	x	x	x	x
+van +hire +milton +keynes	Milton Keynes	x	x	x	x	x
van hire cardiff	Cardiff	x	x	x	x	x
+van +hire +banbury	Banbury	x	x	x	x	x

<tbody>
</tbody>

This way, I can look at the performance of locations rather than that of individual keywords.

Is there a way of searching the text of the keyword to pull out the appropriate branch from a list, without having to create a huge =IF(ISNUMBER(SEARCH("aberdeen",A2)),"Aberdeen",IF(ISNUMBER(SEARCH(.... type formula?

Thanks very much in advance!

Loose Fred

 

[Snakehips]

loosefred,

Is the keyword always +van +hire + the branch ?

If not then can you give examples of other expected strings.

 

[loosefred]

Hi Tony,

The majority of them are - there are also keywords where the string of text is just van hire branch without the +'s. The +'s are there to show what 'match type' the keyword is, and when exported into excel makes things more awkward!

Thanks

Loose Fred

 

[Snakehips]

Loose Fred,

Try this......

Excel 2007
	A	B	C
1	Keyword		Transactions
2	+van HIRE +ABerDEEn	Aberdeen	x
3	+van +hire +coventry	Coventry	x
4	+van +hire +nottingham	Nottingham	x
5	+van +hire +sheffield	Sheffield	x
6	+van +hire +milton +keynes	Milton Keynes	x
7	van hire cardiff	Cardiff	x
8	+van +hire +banbury	Banbury	x
Sheet3

Cell Formulas
Range	Formula
B2	=PROPER(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(LOWER(A2),"hire",REPT(" ",200)),"+"," "),100)))

Hope that helps.