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

Top 10 list of names based on sales totals

I

indesignkat

Member
Hello, wonderful site here.


(hypothetical story that illustrates my dilemma)


I've got a list of ~200,000 accounts, handled by ~300 salespeople, and I generate reports weekly to see how much each account has ordered. I put that data into a table and it's easy to sort and filter to find the top 10 accounts per salesperson, but with so many salespeople it does take a long time for me to do. I'd like excel to do the work for me.


Exactly what I'd like is to say "Bob's accounts" and then the next 10 rows list the top 10 accounts that Bob handles, ranked by their order totals. I don't want it to show me the order totals, though, I want it to show me their account number.


The closest I've gotten so far is with this array formula. It works, but only if the top 10 list starts in row 1 of the sheet. If it starts on row 4, for example, it starts with the 4th ranked account. The top 10 report I need to generate has been in the same format, done by manual sorting & copy/paste, for over a decade, so I can't change the format of the report.


'=INDEX(A$1:C$27,MATCH(LARGE(IF(C$1:C$27=D$1,B$1:B$27),ROW()),B$1:B$27,0),1)'


The spreadsheet for this example has account numbers in column 1, order totals in column 2, and salesperson name in column 3. D1 has the salesperson who's list I'm trying to generate.


Can anyone explain what I need to do to remove the 'list has to start on row 1' restriction?


thanks,


idk
 
Hi ,


The basic thing to be understood is the parameters of the LARGE function ; this takes 2 parameters , the first being an array , and the second being a number or an array. If we take the case of the second parameter being a number , then :


=LARGE(array,1) will return the largest number from the first array parameter.


=LARGE(array,2) will return the second largest number from the array parameter


=LARGE(array,3) will return the third largest number from the array parameter


and so on.


In a formula which is to be copied downwards , the first row should have the second parameter as 1 , the second row should have the second parameter as 2 , the third row should have the second parameter as 3 ,...


The easiest way to get this is to use ROW(A1) for the second parameter ; thus , if you use the formula :


=LARGE(array,ROW(A1))


and copy it downwards , the formula will change to give you the second , third , fourth ,... largest numbers from array.


Using ROW() returns the row number of the row in which the formula has been put ; thus if you are in cell J27 , and you use ROW() in that cell , it will take the value 27.


Narayan
 
Excellent, thank you very much. I was trying ROW() or ROW(1), not thinking it must be a cell reference. I appreciate the explanation.


idk
 
You must log in or register to reply here.
Back
Top