I've been working & researching this situation for hours and hours but can't crack it. I'd appreciate your help!
Here's what I have:
(see image below)
Here's what I want but cannot figure out the formula or find a solution online:
(see image below)
A new table with a formula that will pull the following info from the existing table:
Example:
Here is an illustration showing the existing table of data on top, and the new table I want the formula to build below it:
FYI: I'm using Excel for Mac 2011 version 14.3.8
Thank you in advance for your help!!
Here's what I have:
(see image below)
- An existing table of web analytics data with rows containing unique keyword-search engine combinations (example: B3 is "a keyword", C3 is "google"; B4 is also "a keyword", but C4 is "yahoo").
- Along with keyword and search engine, each row has columns for total number of impressions, one or more unique landing page URLs (each unique URL appearing more than once in multiple columns), and other columns to be ignored.
Here's what I want but cannot figure out the formula or find a solution online:
(see image below)
A new table with a formula that will pull the following info from the existing table:
- For every keyword (column B) that's associated with Google (column C), print one instance of each unique URL in its own row, along with the URL's associated keyword, and associated number of impressions.
Example:
- In the existing table, the first row contains "a keyword" (B3) that's associated with "google" (C3), an impression count of 115, the URL http://site.com (which appears in 2 columns), and the URL Website Design | Small Business Web Design (which appears in 1 column)
- In the new table, the first row would print: http://site.com - "a keyword" - 115
- The next row would print: Website Design | Small Business Web Design - "a keyword" - 115
- Since those were the only unique URLs in the existing table's row 3, the next row in the new table would move on to the next keyword associated with Google: Website Design | Small Business Web Design - "another keyword" - 50; http://site.com - "another keyword" - 50
- And so on through the existing table
Here is an illustration showing the existing table of data on top, and the new table I want the formula to build below it:
FYI: I'm using Excel for Mac 2011 version 14.3.8
Thank you in advance for your help!!