Index Match Rank with Multiple Criteria

Hi - I'm struggling with this and am looking for some assistance if possible.

I have attached a workbook which clearly outlines the issue and set up two PT's which are set tp show the expected outcome.

In range C38:H42 of the Summary tab I would like to pull data for the top 5 ranked sources (column C), of the "Traffic Sources" tab.

Ranking is based on the highest number of Sessions,(column E) of the "Traffic Sources" tab.

I would like this selection to be based on multiple criteria - the criteria are Medium (based on the combobox list) Linked cell = AC32 and Week Number = AC29

I have produced two PT's which are set to show the expected outcome ----

Any help would be appreciated

Many thanks

I'm not sure where you are going with this and the week requirement I can't test very well but I think that this gets what you want.

If there was something other than the week # and the sessions that is unique to find the largest 5 items because there can be multiple ties in rank a formula like this adapted to your data would work

Formula:

Please Login or Register  to view this content.

Wow, I can see that you have put some considerable time and energy into this - many many thanks..! What you have produced for E32:H38 is spot on but C32:D38 is not looking at the source column which is col C of the traffic sources tab..

C32:D38 should read..

lnkd.in 51
intranet.vesuk.local 27
linkedin.com 18
t.co 13
flipboard.com 4

if tried to amend your formula in C61

Please Login or Register  to view this content.

to pick up the Source Col C of the traffic sources tab but without luck!

Hey NDM, I appreciate your effort and realise that any further work on this may be a ball ache, so no worries if you cant pick this up again!

All the best Sir

I don't follow the cell references that you give because they just don't match the workbook that I uploaded. Is this another workbook that you are getting the cell references from?

You are talking about C32:D38 but on the workbook that I have that is mostly empty space relating to nothing. I think that you mean C38:D42. I found a mistake in my formula and have corrected it. The formula in C61 should be:

Formula:

Please Login or Register  to view this content.

I cannot however find the values of 51, 27, 18, 13 and 4 on the Traffic Sources worksheet. You have 2 column labelled Sessions in the range B37:H37 but there is only one column in the Traffic Sources labelled Sessions. Where are these figures that you quote coming from? If I knew the column from Traffic Sources for the figures, I could easily amend the formula to include them but I can't find them anywhere.

lnkd.in 51
intranet.vesuk.local 27
linkedin.com 18
t.co 13
flipboard.com 4

You say that you want the top 5 ranked Sources (column C but you want the ranking based on the Medium column D as indicated by AC32 and the weeknumber in AC29). If records a filtered by the medium and the weeknumber, you will not necessarily get the values you list for column C. There has to be something missing in order to pull the records that you list and I can't find.

ok - many many thanks for your patience and for responding... this is really not as complicated as i've probably lead you to believe but its too complicated for my excel skill set! What i'm doing now is preparing a spreadsheet which is crystal clear to try to get my point across - but to satisfy your curiosity until that point I can tell you that the week number column and medium column in the data table are the criteria for selecting the data to extract ie for week X and medium y what were the top five sources by sessions and for each source... what was the most referred to story.

I'll explain it better in a workbook! I just didnt want you to think I was ignoring your response!

Hi - I have uploaded a new workbook which is hopefully clearer! I believe I have covered all of your questions!! if not feel free to shout - this is not urgent btw so please relax and enjoy the challenge!!

Good luck in -8!!!

In response to your questions -

I cannot however find the values of 51, 27, 18, 13 and 4 on the Traffic Sources worksheet - these figures are aggregated...


You have 2 column labelled Sessions in the range B37:H37 but there is only one column in the Traffic Sources labelled Sessions - one is the aggregated info and the other was the page with the highest views

I think that this has been made clearer in my recent upload - once you have had a look, and if you feel its too much of a ball ache then please feel free to drop it! I totally understand!!!

I think that this should work for you. I tried to make it work with the workbook that you uploaded yesterday but somehow, when I transferred my work from the older workbook, the macros failed. This file at least works on my computer and I hope that it works on yours.

I finally got your latest workbook to work with the data to fill in the area that you wanted. When I copied my work from one workbook to the other, it retained references to the first one and I didn't correctly get rid of them. Now it works.

Hi R, I had no idea the trouble you had to go through to achieve this - it looks like an enourmous amount of work and trouble. I can't thank you enough for the time you have taken. I supposed that it would be a forumula in a cell referencing the tables, i didn't realise you would have to create a ranking table - thanks so much for your effort.... i'm close to tears thinking that someone I have never met would go to this length to help!

All the best

D

The biggest challenge was to figure out the methodology. After that it was just a matter of filtering the records by use of formulae. The first table to pull all records that matched the criteria and the second to extract from that the highest ranking unique records.

Glad to do it for you my friend. I was watching Doc Martin at the time so it wasn't all hard work