• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to match text strings to corresponding data to create a calculation?

tolbit

New Member
How to match text strings to corresponding data to create a calculation?


Hello, William here. I am a Web Analytics specialist and use excel allot!


Naturally after building a spreadsheet for my customer they asked for another that I am not sure how to proceed.


I have 50K line table with Search Phrases data (back to FY2004) in the following format:

year, Rank, Phrases, Engines, Referrals, % Referrals, Source, Status, Comment


Because I am a government contractor all original data has to be displayed in the data set, therefore, rank, % Referrals is just junk data and can be forgotten about.


Year - this field is Fiscal Year or Fiscal Year + Quarter for example FY2008, FY2009, FY2008-Q1 and so on.

Phrases - is the search phrase from a commercial search engine such as Google or yahoo and looks like "renewable energy"

Engines - list the specific engine that the search phrase came from - the only one I am interested in is "total"

Referrals - how many times the phrase was used

Source - there are a couple of web sites that was combined into the list

Status - this was a through back from another table where I can discard or retain a specific lines as need be.


Therefore, the data looks like this:


Year Rank Phrases Engines Referrals % Referrals Source Status Comment

FY2004 1 renewable energy Google 4,777 0.4930% EERE Total Discard

FY2004 1 renewable energy Yahoo 2,988 0.3080% EERE Total Discard

FY2004 1 renewable energy MSN 1,338 0.1380% EERE Total Discard

FY2004 1 renewable energy Google Australia 761 0.0790% EERE Total Discard

FY2004 1 renewable energy Google UK 737 0.0760% EERE Total Discard

FY2004 1 renewable energy Google Canada 262 0.0270% EERE Total Discard

FY2004 1 renewable energy Google Germany 167 0.0170% EERE Total Discard

FY2004 1 renewable energy Yahoo Hong Kong 118 0.0120% EERE Total Discard

FY2004 1 renewable energy AOL NetFind 111 0.0110% EERE Total Discard

FY2004 1 renewable energy Yahoo Australia & NZ 93 0.0100% EERE Total Discard

FY2004 1 renewable energy Other 494 4.1700% EERE Total Discard

FY2004 1 renewable energy Total 11,846 1.2220% EERE Total Retain


I am being asked to build a chart that allows our search manager to select two time frames and then produce the difference and percent. For example compare FY2008 to FY2009 and show the change in difference between the usage of the specific search phrases.


I thought I could use pivot tables but this did not work out as I had hoped. The difficulty is not displaying the first year data but matching up the comparison year search phrase data to compute the difference.


I need a table that would look like this:


Year: FY2009 (selectable) Year: FY2009 (selectable) Difference % Difference

Search Phrase: Referrals Referrals

Renewable energy 11,846 12,234 388 xx%

Bla

Bla

Bla


Any suggestions on how I can match up the second years data to the first years search phrase (text) to complete the calculation?


Thanks,

William
 
Tolbit

I would try something along the following lines

1. Setup another area where you can query the data and select the Date Range or alternatively use a Data Table to store the data, as that allows selection of Ranges

2. Extract all the unique Search terms from this Date Range, Use Data, Advanced Filter into a separate table

3. Now use a sumproduct() or Countif formula to fill the rest of the columns in using the Unique Values as search items, searching the original Date Selected List

4. Add what ever other columns you need for the report


Try it manually initially and then you can automate if you need to later on.
 
Back
Top