LinkBack URL
About LinkBacks
Afternoon all,
I'm assuming I'll need an array formula for this but how could I summarise and rank the attached data to show the top 5 customers and their related sales total?
Thanks in advance,
Snook
Afternoon all,
I'm assuming I'll need an array formula for this but how could I summarise and rank the attached data to show the top 5 customers and their related sales total?
Thanks in advance,
Snook
Last edited by The_Snook; 10-31-2016 at 05:01 AM.
Hi Snook,
if you want to avoid array formulae, you can do it like this:
Insert 4 new columns between B and C, and put this formula in C2:
=IF(COUNTIF(A$2:A2,A2)=1,MAX(C$1:C1)+1,"-")
Copy this down to the bottom of your data by double-clicking the fill handle on C2.
Leave column D blank (just to separate things), then put this formula in E2:
=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),C:C,0)),"")
and this one in F2:
=IF(E2="","",SUMIF(A:A,E2,B:B))
Copy these down until you start to get blanks (I've copied to row 15 in the attached file). These will give you a unique list of customers along with the spending for each. Then you can use this formula in J4:
=LARGE(F:F,H4)
and this one in I4:
=INDEX(E:E,MATCH(J4,F:F,0))
and copy these down to row 8. You can hide columns C to F if you want the file to look like the one you submitted. Note there is no tie-break on the spends, but I figured that it would be highly unlikely that you would have ties given the amount of data that you have.
Hope this helps.
Pete
See if this helps...
How to generate a top N list
https://www.excelforum.com/showthread.php?p=3396886
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
This solution uses four helper columns, which may be hidden for aesthetic purposes.
This solution addresses the point that the link to Tony's post raises, which is the case of two customers having the same sales total.
The first helper gets an unique list of the customers using: =IF(COUNTIFS(A$2:A2,A2)>1,"",A2)
The second helper filters the unique list of the customers using: =IFERROR(INDEX(H:H,AGGREGATE(15,6,(ROW(H:H))/(H:H<>""),ROW(A1))),"")
The third helper sums the sales by customer using: =SUMIFS(B:B,A:A,I2)
The fourth helper adds .001 of the row number to break ties in sales totals using: =J2+ROW()*0.001
The sales column of the table is populated using: =INDEX(J:J,MATCH(LARGE(K:K,D4),K:K,0))
The customer column of the table is populated using: =INDEX(I:I,MATCH(LARGE(K:K,D4),K:K,0))
Let me know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Each instance of that formula is having to process more than 2 million cells, an incredible amount for a single formula. Unless the OP actually has data extending all the way to row 1048576, I strongly suggest you reduce the ranges being passed to AGGREGATE.Originally Posted by JeteMc
Regards
XLenent point. Here is a work around that hopefully reduces the number of calculations, at least my machine was able to get the results MUCH faster. I have added an array entered formula to find the last row in column H that is not blank using:The formula in column I then uses that row number to restrict the range using the formula:Formula:
Please Login or Register to view this content.Let me know if you have any questions (or other suggestions).Formula:Please Login or Register to view this content.
Get unique IDS
=IFERROR(INDEX($A$2:$A$1000,MATCH(0,INDEX(COUNTIF(H$3:$H3,$A$2:$A$1000),0,0),0)),"")
H4 and copy down
Sales
in I4 and copy down
=SUMIF(A:A,H4,B:B)
in E4 and copy down
=INDEX($H$4:$H$100,MATCH(LARGE($I$4:$I$100,ROWS($1:1)),$I$4:$I$100,0))
in F4
=VLOOKUP(E4,$H$4:$I$100,2,0)
Thinking on this a little further what about a pivot table with the customer names as row labels, sales [sum of] as values, shown in tabular form, value filtered - top 5, and sorted in descending order by sum of sales?
Morning all,
Apologies for the delayed response I got sidetracked on something else.
Thanks for all your help/suggestions, as always it gratefully received.
Regards,
Snook
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks