Ranking formula Help

hi dilipandey

the order you provided is correct
Sorry for the typo

Regards

D

---------- Post added at 01:11 PM ---------- Previous post was at 11:30 AM ----------

Any1 got any ideas ?[COLOR="Silver"]

could anyone help me with above problem ?

I made a pivot table for you that looks at your staff's names on the vertical and the MCC code on the horizontal then used a sum of count of MCC code for the data.

To rank them I used the following formula {=IFERROR(RANK(B5:B9,B5:B9,0)}

In order to rank you need to represent the data as an array or it will not return the value you want.

Here is the file. Is this what you needed to accomplish? Query4(1).xlsx

You can do it in a pivot table. Take a look at the attached and let me know if this is what you were looking for you.

HI guys

thank you for responding back to this post. Both of you provide excelent solutions
day92- i love the idea of a pivot table, however this raw data is comming from access, and i need to sumarise this data, so i can put it back into access and carry on sumarising this info. so unfortunately it wont work,so i need to have a ranking system like "the nash effect"

the nash effect, as the mcc codes will contiunely grow same as the rm code/Seller code, is there anyway, this could be done via a macro, so regardless of ammount of data acess kicks out, the macro, will look at this, and sum it up the way you have done it ?

I haven't learned VBA yet so I can't give you any further insight into building a macro.

Without one, The only thing I could suggest would be to set your pivot table ranges to extend to the end of the work sheet and that way as new MCC codes are generated and your data set grow, then the pivot table will caputure them when you update the data set and refresh the table.

As is for the rank you would just carry the formula out as far as the pivot table goes. You can orient it however you want and perhaps there is a better way. One particular issue that I see happening is the size of the staff increasing and that would not account for all the people in the rank. You just adjust the ranges accordingly to fix that. There are a lot of really talented VBA people on here who might be able to take what you have so far and figure out a way to automate the procedure but this is about as far as I can go. Happy to help and good luck.

HI "the Nash effect" the worksheet i uploaded should have the following headings
RM_Code MCC_Code
CountOfMCC_Code
SellerName
MCC Description

These column names will always remain the same, as this is what i am exporting from access to excel. Its just the numbers of rows which will expand.
Is there a way, we could get the ranking system to work without the pivot table as a reference ?

Without a pivot table you can use the attached.

Micope21

That is exactly what i want
Is there a way, that we could get the results automated via a macro ?

So eg, i run the macro

and it develops, the layout, 2nd macro, will provide the results etc ?

micope21

I appreciate your help If you ever need a favour returned , send me a msg and i will try and help