Top 20 with twist

  • Thread starter Thread starter Rosie
  • Start date Start date
R

Rosie

Hi folks,

I have a speadsheet of about 50,000 lines of web statistics
and 20 columns of info .. from client name ... to pages
hit ect. What I need help with is the following

in A1-A20 I want to enter the names of the top twenty
clients who have hit a particular selection of webpages.

I'm sure it's dead easy ... but too wired to think. Let me
know if you need more information.

Thanks in advance
Rosie
 
Ok interesting ... However I'm going to need to bed the
rank forumla in with a sum product one for what I want.

EG: Company1 appears twenty times, but accesses webpage1
five times, whilst Company2 appears six times, and
accesses webpage1 six times, by webpage ranking Company2
ranks higher than Company1 - I reckon I can figure that
out.

BUT .. That doesn't help me get rankedcompany1 in cell A1
and rankedcompany2 in cell 2, bearing in mind that
different companies will be ranked differently based on
which webpagesection i'm interested in. So when I look to
a different web page ranking say in cells b1-b20, or my
data changes in realtime (which it will) I'm stuffed. Any
ideas welcome -more information available if people need
it.

This may sound ambitious but all i need is formulas so I
can hard wire it into a program to do it automatically.

All and any help gratefully received.

Cheers
Rosie
 
...
...
EG: Company1 appears twenty times, but accesses webpage1
five times, whilst Company2 appears six times, and
accesses webpage1 six times, by webpage ranking Company2
ranks higher than Company1 - I reckon I can figure that
out.

BUT .. That doesn't help me get rankedcompany1 in cell A1
and rankedcompany2 in cell 2, bearing in mind that
different companies will be ranked differently based on
which webpagesection i'm interested in. So when I look to
a different web page ranking say in cells b1-b20, or my
data changes in realtime (which it will) I'm stuffed. Any
ideas welcome -more information available if people need
it.
Click to expand...

Respond to the responses you receive, not to your original posting.

If column A of your table of data records contains the company names you want in
column A of your results, and if column G of your table of data records contains
the number of hits on the particular web page you're interested in ranking, then
with your table named TBL, the name of the complany with the most hits would be
given by

=INDEX(TBL,MATCH(MAX(INDEX(TBL,0,7)),INDEX(TBL,0,7),0),1)

If this company name should appear in cell A2 in a different worksheet, and
noting that LARGE(A,1) == MAX(A), use the formula

OtherWorksheet!A2:
=INDEX(TBL,MATCH(LARGE(INDEX(TBL,0,7),CELL("Row",A1)),INDEX(TBL,0,7),0),1)

Select A2 and fill down into A3:A21.

If your other columns contain information other than number of hits on
particular web pages, provide details on what *EXACTLY* they contain. There are
ways to rank conditional counts, but having to guess your actual table layout is
too big a PITA to undertake.
 
Back
Top