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:

[TABLE="width: 613"]
<tbody>[TR]
[TD]Keyword[/TD]
[TD]Transactions[/TD]
[TD]Revenue[/TD]
[TD]Clicks[/TD]
[TD]Impressions[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]+van +hire +aberdeen[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +coventry[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +nottingham[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +sheffield[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +milton +keynes[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]van hire cardiff[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +banbury[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 827"]
<tbody>[TR]
[TD]Keyword[/TD]
[TD]Branch[/TD]
[TD]Transactions[/TD]
[TD]Revenue[/TD]
[TD]Clicks[/TD]
[TD]Impressions[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]+van +hire +aberdeen[/TD]
[TD]Aberdeen[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +coventry[/TD]
[TD]Coventry[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +nottingham[/TD]
[TD]Nottingham[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +sheffield[/TD]
[TD]Sheffield[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +milton +keynes[/TD]
[TD]Milton Keynes[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]van hire cardiff[/TD]
[TD]Cardiff[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
[TR]
[TD]+van +hire +banbury[/TD]
[TD]Banbury[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[TD="align: right"]x[/TD]
[/TR]
</tbody>[/TABLE]

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.