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

Help with Index (i think!)

F

Flashart80

New Member
Hi All


I am loving this site with all of its amazing knowledge. I am hoping someone can help me with my project. Whilst I am trying to learn more and more, this project is time sensitive and I am throwing it open to more knowledgeable people. By the way, I am using Excel on mac, Version 2011, though I am avoiding macros as I am the only one in my team that has this version!


Ok here's the lowdown. I have a large dataset in one worksheet (called report_data) that spans 11 columns and around 20,000 rows. I have a dynamic named range called data that spans these columns and rows. The data grows and shrinks every week (hence the dynamic named range).


Further to this I have individual dynamic named ranges for each column, such as data_account, data_impressions etc.


I have another worksheet (dashboard) which I am using to build a excel dashboard. Currently I have lots of pivot tables that are pivoted to show different sets of data. This is fine but having seen what can be done on this site, I want to get a little bit more control rather than just amending a pivot.


The dashboard is reporting on Adwords PPC data and the structure of this is that you have an account which houses campaigns which house adgroups.


On worksheet B I have a date range which looks up the last 7 days using this forumula: =MAX(report_data.xls!data_date) which gives me the latest date (a sunday). This date will only change every Monday once the data is refreshed as it's for a weekly report.


I then subtract 6 to give me the Monday for that week. I'm sure there is a better way of doing it!


What I want to achieve is a formula that returns all the campaigns and its metrics for an account name that I enter, for the date range. For example: if i enter Account A in cell A:1 the formula returns in A:5:


A:4| Campaign Impressions Clicks etc

A:5| Campaign 1 2300 24 etc


There are 5 kip's that I would want returned.


The number of campaigns varies depending on the account so I need a formula that instead of displaying an error, displays nothing instead.


I have experimented with Index and match with no success! Please help! Kind regards

Peter
 
Welcome to the forums!


I'm afraid it's a little unclear as to what exactly you're wanting. Could you post a brief example of what the data table looks like, and what you'd like to see? It sounds like you want a report of x records for a given campaign, but I don't know where the campaign names are located in data table (and do you want each record displayed, or some type of sum/roll-up?)
 
HI Luke


Many thanks for replying. You are spot on with the rollup. On the report_data sheet I have the data in columns a-k like this:


date | account | campaign | campaign_status | adgroup | adgroup_status | impressions | clicks | cost | avg_pos | conversions


The data is in date order from earliest at the top to latest at the bottom, so for example:


2011-05-01 | grudge | grudge_new | active | sgm-g-s-gr-new | active| 2300 |450 |34.50 | 2.4 | 17

2011-05-01 | grudge | grudge_shoes | active | sgm-g-s-gr-shoes | active| 1800 | 350 | 24.70 | 1.4 | 7

2011-05-02 | grudge | grudge_shoes | active | sgm-g-s-gr-shoes | active| 1200 | 189 | 12.70 | 1.9 | 12


all the way to the last sunday (currently the 12th June 2011).


I need a formula that looks at the date period I enter, then looks at the account I enter, returns all the relevant adgroups for that account and rolls up the kpis, clicks, impressions etc. So in the above example for the date period 01-05-2011 - 02-05-2011 would return:


Account | campaign | Adgroup | impressions | clicks | cost | avg_pos | conversions

Grudge | grudge_shoes | sgm-g-s-gr-shoes | 3000 | 539 | 37.40 | 1.7 | 19


I hope that makes it a little clearer!


Thanks again for helping.
 
This should help you out:

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/


It sounds like you'll need to build some SUMPRODUCT formulas. Something like:

=SUMPRODUCT(--(AccountRange = Account),--(DateRange>=LowestDate),--(DateRange<=HighestDate),(ColumnToSum))
 
Thanks Luke


I'm not sure how this formula will be able to pull out all the campaigns for that particular account for that date period. It seems to be fine if I want an overall accounts performance , I have something similar running though not as elegant:


=SUM(IF(report_data.xls!data_account='Client List'!$B$2,IF(report_data.xls!data_date>=VALUE('Client List'!$G$3),IF(report_data.xls!data_date<=VALUE('Client List'!$G$4),report_data.xls!data_clicks))))


However this doesn't pull out the campaign name. Essentially I need it to say "Here are all the campaigns and their relevant stats (one campaign per row) for the date range you specified". If that makes sense?


Thanks.
 
Have a look at using an Advanced Filter

Easy to setup and will do what you want
 
Hi Hui


Thanks for the reply. Wouldn't this approach limit me to using the sheet the data is in? Unless I am missing something! What I am looking for is the formula equivalents of pivot tables. I currently have many pivot tables that show me a drop down of each account and then when selected I have the campaigns on the left and them metrics in the data section of the pivot table. I configure the date to show only the last week.


Whilst this works, pivots are a little limiting in terms of formatting and customising. With some sort of lookup/summing formula, I can replicate the look of the dashboard more efficiently.


Does that make sense at all?


Cheers

Peter
 
Flash,

Advanced filter is a little quirky about that which sheet you start in. Generally, it can only paste the filter results to the sheet you started in. So, in your case, start on your output sheet, call the autofilter, select the data and criteria(s) from data sheet, and have it returned to the sheet you want.


The other way around that annoyance is to use Named Ranges (similar to how data validation "says" you can't use a list from another sheet), and reference the named range.
 
HI Luke


Thanks for sticking with me on this. Hmm, I think in this case the auto filter is not suitable. All I want the team to have to worry about is refreshing the data in the data sheet and then it will update the results in the other sheet automatically.


Do you have any further ideas on how I can achieve this? I was thinking of an index, offset, match, sum product combo, i'm just not sure how to structure it!
 
Yeah, I've got an idea of what the formula would look like. It'll be a long one though...


Setup:

A2 is account name, B2 is start date, C2 is end date.

Array Formula:

=IF(ROWS($A$4:$A4)>SUMPRODUCT(--($A$2=report_data!$B$2:$B$10),--($B$2<=report_data!$A$2:$A$10),--($C$2>=report_data!$A$2:$A$10)),"",INDEX(report_data!B:B,SMALL(IF((report_data!$B$2:$B$10=$A$2)*(report_data!$A$2:$A$10>=$B$2)*(report_data!$A$2:$A$10<=$C$2),ROW(report_data!$A$2:$A$10)),ROW(A1))))


Array formula needs to be confirmed using Ctrl+Shift+Enter. You should only need to change the references (if needed) I assumed for setup, or to extend the length of any ranges from report_data sheet. The INDEX formula uses a relative reference on col B, so it'll change automitcally to pull in other columns as you copy it to the right. Copy this formula as far down as would ever be needed (unneeded rows will be blank). Essence of formula explained here:

http://chandoo.org/forums/topic/lookup-1

and here:

http://chandoo.org/forums/topic/dynamic-validation-list


Quick summary:

Finds all the rows that meet criteria, spits them out in order using the INDEX/SMALL combo. Downside is that it doesn't sum the records, it simply shows all records that meet criteria. Maybe you could have another row somewhere that sums each respective column?
 
Think I will have to go back to using a pivot. Seems the formula is too much for Excel and it crashes!


Thanks for your help!
 
You must log in or register to reply here.
Back
Top