Remark Marks Rank Pass 495 1

Fail 490 -

Pass 480 2

Pass 475 3

Fail must be ignored from ranking What is the formula ?

There is no built in formula that will eliminate certain values in a range and then rank the rest. You'll need to use a macro.

Assuming your table begins in A1, this macro should provide the Ranking that you are looking for with the inclusion of one problem:

It will not work correctly if there will be "ties" in your list of Marks.

Ties will create a problem and require code that might get pretty complicated since the RANK function doesn't work on Arrays.

Let me know if Ties are a possibility and I'll see what I can do.

Option Base 1 Sub PassingRank() 'Declare Array Dim PassArray() As Integer 'Determine length of data based on Column A lastRw = Range("A" & Rows.Count).End(xlUp).Row 'Dimension Array pass on count of "Pass" numPass = WorksheetFunction.CountIf(Range("A2:A" & lastRw), "=Pass") ReDim PassArray(numPass) 'Clear Column C before each run Range("C2:C" & lastRw).ClearContents 'Populate array with only Passing Marks For rw = 2 To lastRw If Range("A" & rw) = "Pass" Then m = m + 1 PassArray(m) = Range("B" & rw) End If Next 'Use the LARGE function to Rank the array elements 'Find each element in Column B and put it's rank next to it With Range("B2:B" & lastRw) For mRank = 1 To UBound(PassArray) Set m = .Find(WorksheetFunction.Large(PassArray, mRank), lookat:=xlWhole) If Not m Is Nothing Then Range("C" & m.Row) = mRank End If Next End With End Sub

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

Here's a way to do it without a macro, although a macro could be written to make this more automatic. This method will handle Ties in the normal way that the RANK function deals with Ties.

1 - Insert a "helper column" in Column A and fill it with 1, 2, 3, etc. down to the bottom of your list.

2 - Select all of your data, including Column A and Sort it based on the Remark column in Descending order. This will bring all of your Pass cells to the top.

3 - Use the RANK function to RANK only the Pass section of your data.

4 - Copy the RANK results and do a PasteSpecial..Values to lock in the ranking values.

5 - Select all of your data (including Column A and the Rank column) and sort it on Column A in Ascending order. This will put the data back into the original order.

6 - Delete the helper column, Column A.

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

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History