Solved Rank based on a criteria

June 13, 2011 at 07:28:56
Specs: Windows XP
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 89

So 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!


See More: Rank based on a criteria

Report •

✔ Best Answer
June 13, 2011 at 11:17:56
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.



#1
June 13, 2011 at 08:07:00
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 after the 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.


Report •

#2
June 13, 2011 at 08:13:15
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	89

Thanks so much for your help.


Report •

#3
June 13, 2011 at 08:17:32
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


Report •

Related Solutions

#4
June 13, 2011 at 10:08:00
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.


Report •

#5
June 13, 2011 at 10:58:25
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?


Report •

#6
June 13, 2011 at 11:17:56
✔ 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.


Report •

#7
June 13, 2011 at 12:27:13
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

http://www.skeptic.com/


Report •

#8
Report •

#9
June 13, 2011 at 12:39:24
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.


Report •

#10
June 13, 2011 at 13:05:48
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

http://www.skeptic.com/


Report •

#11
June 13, 2011 at 14:35:45
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!


Report •

#12
June 13, 2011 at 15:26:29
Post examples so we can see.

MIKE

http://www.skeptic.com/


Report •

#13
June 13, 2011 at 19:34:51
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.


Report •

#14
June 14, 2011 at 11:57:09
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 Sub

Thanks so much for your help!


Report •

#15
June 14, 2011 at 18:55:44
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.


Report •

#16
June 15, 2011 at 07:05:07
Thank you! This works perfectly!
Again, really apprciate both your help on this!

Report •

#17
June 15, 2011 at 07:29:17
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.


Report •

#18
June 15, 2011 at 12:01:23
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.


Report •

#19
June 15, 2011 at 12:09:23
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, should work 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

http://www.skeptic.com/


Report •

#20
June 15, 2011 at 12:22:25
re: "The range is +/- 10 from what I'm seeing"

I'm not sure what that means.

+/- 10 means what with 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 entry

Is 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.


Report •

Ask Question