Ranking Formula Required

[mikemcbain]

My Friends

I have another DB with a couple of hundred Towns each one with up to 11 areas and they are Ranked by Value in Column J but now I require a formula in Column L to Rank them by Fees?
I have done a couple of Towns manually and put the result that I require into Column M.

Perhaps there is a magician under lockdown that could help me once again?

Stay strong

Mike.
Town Area No. Free Name Charged Junior Senior Value VRank Fees FeesRank
New Town 1 1 Mitchell 5 Chas NM 2027 6 $5.50 3
New Town 1 2 Godbolt 7 Brendan RG 4664 1 $2.00 1
New Town 1 3 Deamer 3 Robert JD 3567 2 $7.00 4
New Town 1 4 Jones 6 Andrew VJ 2229 5 $21.00 6
New Town 1 5 Shultz 4 Glenn SG 1486 7 $35.00 7
New Town 1 6 Taylor 2 Peter ST 3054 3 $4.50 2
New Town 1 7 Wilkes 1 Owen TW 2764 4 $18.00 5
Old Towns 2 1 Foran 2 Michael AF 3068 4 $6.50 3
Old Towns 2 2 Godbolt 14 Ron RG 3225 3 $6.50 3
Old Towns 2 3 Holloway 11 Brendan RH 4023 1 $1.73 1
Old Towns 2 4 Bellamy 7 Paul BB 2942 5 $23.00 6
Old Towns 2 5 Sylvester 1 Robert JS 3670 2 $5.25 2
Old Towns 2 6 Godbolt 5 Tom RG 2826 6 $26.00 7
Old Towns 2 7 Thomas 9 Brian TL 2349 7 $11.00 5
Old Towns 2 8 Luke 9 Ken LT 2349 7 $33.00 8

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. If you can do that, also include the expected results please.

 

[KRice]

One approach is to add a column called OriginalOrder (or anything that you like) and number it sequentially 1 to however many rows you have. Then select the entire set of column headings and activate Data>Sort. Go to the Fees column heading drop down and select Sort > and choose either ascending or descending. This rearranges your table in the preferred sort order. Then apply sequential numbers down the FeesRank column. Finally, resort the table using the recently added OriginalOrder column using the same steps.

Depending on the version of Excel (e.g., 365) that you have, you may have a SORT function available that simplifies this process.

 

[mikemcbain]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. If you can do that, also include the expected results please.

 

[mikemcbain]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. If you can do that, also include the expected results please.

Thanks Peter I have updated my details but for the life of me I cannot get XL2BB to work?
It goes into my Add-ins OK and I have it ticked but cannot find any Mr Excel ribbon or link and certainly can't find any menu items or icon to activate the add-in or whatever it does?

My excuse is that I am way over 80yo but this is yet another new problem for me.

Mike.

 

[Peter_SSs]

It goes into my Add-ins OK and I have it ticked

So you have done this & clicked OK?

But you then do not see this on your Excel ribbon?

If you track to wherever you saved the xl2bb.xlam file, right click & choose properties, do you see this

If so check the 'Unblock' box (if there is one) you see at the bottom right -> Apply -> OK
Then close and re-open Excel
Does that make the MrExcel ribbon appear?

 

[Saba Sabaratnam]

Hi Mike,

You can try this formula.

Please enter the following formula in Cell L2 and copy it down to the last row which has Fees data.

=SUMPRODUCT(--(A2=$A$2:$A$20000),--(J2>$J$2:$J$20000))+1

If you have more than 20,000 rows of data, you will have to expand the formula range ($A$20000 and $J$20000) to accommodate your all rows of data.

Kind regards

Saba

 

[mikemcbain]

So you have done this & clicked OK?

View attachment 9976

But you then do not see this on your Excel ribbon?

View attachment 9977

If you track to wherever you saved the xl2bb.xlam file, right click & choose properties, do you see this

View attachment 9978

If so check the 'Unblock' box (if there is one) you see at the bottom right -> Apply -> OK
Then close and re-open Excel
Does that make the MrExcel ribbon appear?

Peter

I finally got it sorted!
Last night tried all your suggestions still didn't work. This morning discovered that I first had to delete it from my add-ins and then re-add it from the new location following the last update.

Many thanks

Mike

Problem09.xlsx
	N
19
Sheet1

 

[mikemcbain]

Hi Mike,

You can try this formula.

Please enter the following formula in Cell L2 and copy it down to the last row which has Fees data.

=SUMPRODUCT(--(A2=$A$2:$A$20000),--(J2>$J$2:$J$20000))+1

If you have more than 20,000 rows of data, you will have to expand the formula range ($A$20000 and $J$20000) to accommodate your all rows of data.

Kind regards

Saba

G'day Saba

Many thanks for your formula exactly what I wanted.

This Forum is one of the greatest most helpful that I have ever found.

Mike.

 

[mikemcbain]

One approach is to add a column called OriginalOrder (or anything that you like) and number it sequentially 1 to however many rows you have. Then select the entire set of column headings and activate Data>Sort. Go to the Fees column heading drop down and select Sort > and choose either ascending or descending. This rearranges your table in the preferred sort order. Then apply sequential numbers down the FeesRank column. Finally, resort the table using the recently added OriginalOrder column using the same steps.

Depending on the version of Excel (e.g., 365) that you have, you may have a SORT function available that simplifies this process.

Thank you KRICE
I did as you suggested by sorting using 365 and it worked beautifully for my whole database.
Now I will prepare a macro using Record to do it quicker in future updates.

Gratefully

Mike.