Rank across worksheet columns

[collegeitdept]

Hello,

Does anyone know of a way to get the rank of an aggregate of multiple columns across sheets in a workbook please?

I found a non-workable but good formula:
=SUMPRODUCT(--(A$2:A$10+B$2:B$10>A2+B2))+1

BUT! it doesn't allow for a lookup/search variable - the columns are "locked" it does not support an index match formula.

IE: rank current sales number Year to date = Index match first day of year : index match current date *** only do this across 3 worksheets.


Excel 2010 has an excellent new function called: aggregate... but it doesn't support rank!

Here I have the average of all sales year to date from 3 separate worksheets within the workbook:

=AGGREGATE(1,4,INDEX('E! Online US'!$A$12:$L10000,MATCH(DATE(YEAR($B$3),1,1),'E! Online US'!$A$12:$A10000,0),MATCH(G$2,'E! Online US'!$A$11:$L$11,0)):INDEX('E! Online US'!$A$12:$L10000,MATCH($B$3,'E! Online US'!$A$12:$A10000,0),MATCH(G$2,'E! Online US'!$A$11:$L$11,0)),INDEX('Eonline App'!$A$12:$L10000,MATCH(DATE(YEAR($B$3),1,1),'Eonline App'!$A$12:$A10000,0),MATCH(G$2,'Eonline App'!$A$11:$L$11,0)):INDEX('Eonline App'!$A$12:$L10000,MATCH($B$3,'Eonline App'!$A$12:$A10000,0),MATCH(G$2,'Eonline App'!$A$11:$L$11,0)),INDEX('EOL Mobile Web'!$A$12:$L10000,MATCH(DATE(YEAR($B$3),1,1),'EOL Mobile Web'!$A$12:$A10000,0),MATCH(G$2,'EOL Mobile Web'!$A$11:$L$11,0)):INDEX('EOL Mobile Web'!$A$12:$L10000,MATCH($B$3,'EOL Mobile Web'!$A$12:$A10000,0),MATCH(G$2,'EOL Mobile Web'!$A$11:$L$11,0)))



I need an 'aggregate' formula that can do rank.

Can someone please help?

 

[Harry Flashman]

Would there be away for it to be done without breaking it down into two steps please?

This is fairly complicated problem - is hard to say without actually seeing the data. You can send me a workbook if you want. Otherwise maybe if you paste some of the data into this forum you will get more help.

 

[collegeitdept]

I found a sumproduct function that provides the rank... and being that sumproduct can also do a lookup/condition as well maybe sumproduct can be used?

=SUMPRODUCT(--($A$2:$A$100=$A2),--(B$2:B$100>B2))+1


Thanks.

 

[Harry Flashman]

I found a sumproduct function that provides the rank... and being that sumproduct can also do a lookup/condition as well maybe sumproduct can be used?

=SUMPRODUCT(--($A$2:$A$100=$A2),--(B$2:B$100>B2))+1


Thanks.

How is one supposed to use that formula? What does it do.

I just tested by putting random numbers between 1 and 500 into A2:B100 and then entered that formula into C2 and dragged down. It filled each cell with either a 1 or 2. What exactly is it supposed to be ranking? What do the returned values even mean?

 

[collegeitdept]

Please find the file:

https://dl.dropboxusercontent.com/u/15717201/Excel Question.xlsx

 

[collegeitdept]

How is one supposed to use that formula? What does it do.

I just tested by putting random numbers between 1 and 500 into A2:B100 and then entered that formula into C2 and dragged down. It filled each cell with either a 1 or 2. What exactly is it supposed to be ranking? What do the returned values even mean?

Column A is the category/variable... Column B is the data.

So it can rank all Column B rows that has "Sales" in it's associated rows in column A.

 

[Harry Flashman]

Please find the file:

https://dl.dropboxusercontent.com/u/15717201/Excel Question.xlsx

Have you considered consolidating all of the data onto the same spreadsheet and then using a pivottable to analyse your data. A pivot table can aggregate and rank your data with ease.
To consolidate your data all you could insert an new column which shows the spreadsheet origin.

We can still look for a formula solution of course but a pivot table could solve a problem like the one you have shown in that workbook very quickly.


This is what your proposed summary table looks like:

[TABLE="width: 335"]
<tbody>[TR]
[TD]Aggregate[/TD]
[TD]Rank[/TD]
[TD]Rank[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Sales[/TD]
[TD]Units[/TD]
[TD]Palettes[/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22/08/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/08/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5/09/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/09/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3/10/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/10/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17/10/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/10/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


However I have notice the sample data you have provided does not actually have any August dates:

Here is a sample of what a pivot table was able to return after I combined your spreadsheets:

[TABLE="width: 285"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Palettes[/TD]
[TD]Sales[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]7/09/2013[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]124[/TD]
[/TR]
[TR]
[TD]13/09/2013[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]14/09/2013[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]19/09/2013[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]20/09/2013[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]21/09/2013[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]24/09/2013[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]238[/TD]
[/TR]
[TR]
[TD]25/09/2013[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]416[/TD]
[/TR]
[TR]
[TD]26/09/2013[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]27/09/2013[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]230[/TD]
[/TR]
[TR]
[TD]28/09/2013[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]29/09/2013[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]30/09/2013[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD]1/10/2013[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]113[/TD]
[/TR]
[TR]
[TD]2/10/2013[/TD]
[TD="align: right"]516[/TD]
[TD="align: right"]381[/TD]
[TD="align: right"]837[/TD]
[/TR]
[TR]
[TD]3/10/2013[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]79[/TD]
[/TR]
</tbody>[/TABLE]

etc (the data continues on until 24th October 2013)

That is sum but I can turn that into rank with the click of button:

[TABLE="width: 364"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Palettes[/TD]
[TD]Sales[/TD]
[TD]Units[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]7/09/2013[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]13/09/2013[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]14/09/2013[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]19/09/2013[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]20/09/2013[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]21/09/2013[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]24/09/2013[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]25/09/2013[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]26/09/2013[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]27/09/2013[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]28/09/2013[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]29/09/2013[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]30/09/2013[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]1/10/2013[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]2/10/2013[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3/10/2013[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]



Or I can sum by Platform (your worksheet name) and month

[TABLE="width: 397"]
<tbody>[TR]
[TD]Worksheet[/TD]
[TD]Date[/TD]
[TD]Palettes[/TD]
[TD]Sales[/TD]
[TD]Units[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Platform 1[/TD]
[TD]Sep[/TD]
[TD="align: right"]295[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]1149[/TD]
[TD="align: right"]1836[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct[/TD]
[TD="align: right"]639[/TD]
[TD="align: right"]395[/TD]
[TD="align: right"]1011[/TD]
[TD="align: right"]2045[/TD]
[/TR]
[TR]
[TD]Platform 2[/TD]
[TD]Sep[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]295[/TD]
[TD="align: right"]552[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]348[/TD]
[TD="align: right"]536[/TD]
[TD="align: right"]1074[/TD]
[/TR]
[TR]
[TD]Platform 3[/TD]
[TD]Sep[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]257[/TD]
[TD="align: right"]309[/TD]
[TD="align: right"]655[/TD]
[/TR]
[TR]
[TD]Platform 4[/TD]
[TD]Sep[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]280[/TD]
[/TR]
[TR]
[TD]Platform 5[/TD]
[TD]Sep[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]228[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct[/TD]
[TD="align: right"]198[/TD]
[TD="align: right"]284[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]790[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]1641[/TD]
[TD="align: right"]2061[/TD]
[TD="align: right"]3913[/TD]
[TD="align: right"]7615[/TD]
[/TR]
</tbody>[/TABLE]

I know this is not what you asked for but it a potential is solution in case an MVP can't help you (I am no where near as good as those guys). And at least it keeps your post visible - someone else might see it and offer a better solution.

 

[Harry Flashman]

Column A is the category/variable... Column B is the data.

So it can rank all Column B rows that has "Sales" in it's associated rows in column A.

Thank you. I had a feeling it might be something like that. Rank by segment.

That is a useful formula. Thank your for sharing. I have a formula that does a similar job. But it is an array formula. Yours is simpler.

=SUM(N(B2<=IF(A2=Segment,Values,-1E+40)))

(Segment and Values are named ranges).

 

[collegeitdept]

I can't do that... that's not even the spreadsheet I will be using. it's full of random data.

The actual corporate file is huge and I cannot modify because other users.



Again what's needed is to find the ranking of all "sales" columns from all worksheets.

Can any MVP help please?

 

[collegeitdept]

If there's an advanced Excel user that can help, please see my new thread here:

http://www.mrexcel.com/forum/excel-questions/734950-excel-mvp-needed-please-help-aggregate-rank.html