Hi,

I'm stuck on a ranking excercise. I'm familiar with the basic ranking function. I'm trying to rank data in a column based on specific criteria. An example data is below:

Item # Level Sales

A 0 12

B 0 14

C 0 15

D 0 4

E 1 18

F 1 3

G 1 45

H 2 4

I 2 89So for each of the item #s, I would like to give it a rank based on the category in which it belongs.

I'm not sure how to go about creating a formula for this. Any ideas are much appreciated!Cheers!

✔ Best Answer

This appears to work for the example data you posted. Assuming your table is in A1:C10 (Row 1 contains column headings) this will place your rankings in Column D:

Option Explicit Sub RankByCategory() Dim lastRw, firstRw, nxtRw As Integer Dim myLevel As Range 'Determine last row in table lastRw = Range("A" & Rows.Count).End(xlUp).Row 'Intialize Row Counter firstRw = 2 'Loop through range looking for change in Column B For Each myLevel In Range("B2:B" & lastRw) 'Increment last row counter nxtRw = nxtRw + 1 'Build and Place formula at change of value If myLevel <> myLevel.Offset(1, 0) Then Range("D" & firstRw & ":D" & firstRw + nxtRw - 1).Formula = _ "=RANK(RC[-1],R" & firstRw & "C3:R" & firstRw + nxtRw - 1 & "C3)" 'Set start row for next set of values firstRw = firstRw + nxtRw 'Reset last row counter nxtRw = 0 End If Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Please click on the last line of this post, read the instructions and then repost your data so that the columns line up. An example of your data

afterthe desired ranking would also be helpful so that we know exactly what you are looking for.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi, sorry for the unclear formatting. Here is the formatted table. Item # Level Sales A 0 12 B 0 14 C 0 15 D 0 4 E 1 18 F 1 3 G 1 45 H 2 4 I 2 89Thanks so much for your help.

And the example of the after the desired ranking is below. Essentially ranking items within each of the Levels seperatly. Item # Level Sales Rank A 0 12 3 B 0 14 2 C 0 15 1 D 0 4 4 E 1 18 2 F 1 3 3 G 1 45 1 H 2 4 2 I 2 89 1

I don't think there is any way to rank them by category without VBA. AFAIK there is no formula that will read down a column and build it's own range based on a change in value. In other words, you can't expect Excel to change the range it is ranking with each change in the values in Column B.

Is this a homework problem?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thanks so much for your quick reply!

I suspected that there is no simple way to do this. This is not a homework problem. I'm analyzing data for a project for my job and trying to basically determine the rankings based on these levels so I can decide which item #s to keep/remove. I'm dealing with 1000+ data so it is very tedious to do this manually.So VBA is the way to go in this situation?

This appears to work for the example data you posted. Assuming your table is in A1:C10 (Row 1 contains column headings) this will place your rankings in Column D:

Option Explicit Sub RankByCategory() Dim lastRw, firstRw, nxtRw As Integer Dim myLevel As Range 'Determine last row in table lastRw = Range("A" & Rows.Count).End(xlUp).Row 'Intialize Row Counter firstRw = 2 'Loop through range looking for change in Column B For Each myLevel In Range("B2:B" & lastRw) 'Increment last row counter nxtRw = nxtRw + 1 'Build and Place formula at change of value If myLevel <> myLevel.Offset(1, 0) Then Range("D" & firstRw & ":D" & firstRw + nxtRw - 1).Formula = _ "=RANK(RC[-1],R" & firstRw & "C3:R" & firstRw + nxtRw - 1 & "C3)" 'Set start row for next set of values firstRw = firstRw + nxtRw 'Reset last row counter nxtRw = 0 End If Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Try this,

it is an ARRAY formula so you must you CTRL-SHIFT-ENTER for it to work.=SUM((C2<=$C$2:$C$10)*1*(B2=$B$2:$B$10))

MIKE

umm...What's the *1 for? It doesn't seem to be needed based on the example data. Is it required for some other data that you tested?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You are correct, the 1 is not needed.

Did not do a lot of testing.

Thought I needed it.I was reading Pearsons' page on Arrays, after giving up on OFFSET and INDEX , etc. and it pointed me in the right direction.

MIKE

Thank you Mike and DerbyDad03. I really appreciate your assistance. I was able to use the formula and seems to have worked to some extent.

I had previousley used the rank function to manually compute the ranks per category by manually specifiying the ranges per category. When comparing the output of the manual and array, I found some differences in results. Some of the valus are exactly the same but some can have an absolute difference of up 40.Any thoughts?

Again, thanks for your help!

Did you try the macro? If so, did the results agree with either Mike's array formula or your manual method or were they different still?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I reviewed my manual ranking and I made a copy/paste error. So using the Array formula, seems like it works for the most part. There is a slight difference (+/- 2) which I will probably attribute to human error :) Also, I did try to run the VBA code but I'm getting a run-time error 1004: application defined or object defined error. I think its got something to do with the way I modified the vba code to fit my spreadsheet because it worked fine with a temporary workbook that only contains the test data I provided above. I'm not familiar with VBA so not sure how to resolve it unfortunately. Here is the modified code (first data line is row 3, the Level column is column A, Sales is column H, and Rank is column N" In case you have suggestions to resolve it:

Option Explicit Sub RankByCategory() Dim lastRw, firstRw, nxtRw As Integer Dim myLevel As Range 'Determine last row in table lastRw = Range("A" & Rows.Count).End(xlUp).Row 'Intialize Row Counter firstRw = 3 'Loop through range looking for change in Column A For Each myLevel In Range("A3:A" & lastRw) 'Increment last row counter nxtRw = nxtRw + 1 'Build and Place formula at change of value If myLevel <> myLevel.Offset(1, 0) Then Range("N" & firstRw & ":N" & firstRw + nxtRw - 1).Formula = _ "=RANK(RC[-1],R" & firstRw & "H3:R" & firstRw + nxtRw - 1 & "H3)" 'Set start row for next set of values firstRw = firstRw + nxtRw 'Reset last row counter nxtRw = 0 End If Next End SubThanks so much for your help!

You were close! Where you have this:

"=RANK(RC[-1],R" & firstRw & "H3:R" & firstRw + nxtRw - 1 & "H3)"you should have this:

"=RANK(RC[-6],R" & firstRw & "C8:R" & firstRw + nxtRw - 1 & "C8)"Since the formula within VBA is written in R1C1 notation, the "C" doesn't mean Column C it means Column(whatever number follows it).

e.g. C1 means Column A, C3 means Column C, C8 means Column H.

In addition, with your example data in Column C and the Ranking formula in Column D, C[-1] means look back 1 column. Since you are now ranking data from Column H in Column N, the formula has to look back 6 columns by using C[-6].

Try this:

Start the macro recorder and enter a RANK formula manually to rank some values. Then go look at what the recorder wrote.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you! This works perfectly!

Again, really apprciate both your help on this!

You haven't answered our question - you owe us at least that much. ;-) You said that Mike's array formula gave you different results than your manually entered RANK formulas. I asked if the macro gave you results that matched either one or something different still.

Have you been able to get the same results via all three methods?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Sorry about that! There is a difference between the array formula output and the VBA. The range is +/- 10 from what I'm seeing, but for the most part the ranking is the same except for a few rows where there is a difference.

We're interested in Why there are differences. What are the parameters that exist that make my Array formula not work correclty or as expected, and the same for DerbyDads VBA.

My Array formula,

shouldwork for all cases. I'd be interested in why it does not.How does the output of my Array formula, differ from the VBA macro?

MIKE

re: " The range is +/- 10 from what I'm seeing"I'm not sure what that means.

+/- 10 means

whatwith regards to Ranking? Is the array formula ranking values out of order by +/- 10 places? Is it just one (would actually have to be a multiple of 2) rank that is wrong or is it all messed up?In addition, there were also 3 methods discussed:

Array Formula

VBA

Your manual entryIs it only the Array Formula that gives different results? Since my VBA should be putting the same RANK formula that you would manually enter in the cells, can I assume that the VBA results match your "manual" results exactly?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History