Numerical Series based on another column numberical values, with duplicates.

[blbat]

I have a column of items that have a weighted ranking value in column "C", and I need a numerical series rank in column "A" that is based on column "C".
Column "C" is always sorted smallest to largest.

Excel Workbook
	A	B	C
1	Numerical Rank	ITEM	Weighted Rank
2		Item to be ranked 1	5.56
3		Item to be ranked 2	21.60
4		Item to be ranked 3	21.95
5		Item to be ranked 4	22.05
6		Item to be ranked 5	22.06
7		Item to be ranked 6	22.30
8		Item to be ranked 7	22.30
9		Item to be ranked 8	22.38
10		Item to be ranked 9	22.50
11		Item to be ranked 10	22.50
12		Item to be ranked 11	22.50
13		Item to be ranked 12	23.54
14		Item to be ranked 13	23.56
15		Item to be ranked 14	23.75
16		Item to be ranked 15	23.95
17		Item to be ranked 16	23.95
18		Item to be ranked 17	24.06
19		Item to be ranked 18	24.06
20		Item to be ranked 19	24.06
21		Item to be ranked 20	24.25
22		Item to be ranked 21	24.26
23		Item to be ranked 22	24.35
24		Item to be ranked 23	24.45
25		Item to be ranked 24	24.70
26		Item to be ranked 25	25.05
27		Item to be ranked 26	25.11
28		Item to be ranked 27	25.35
29		Item to be ranked 28	26.20
Sheet2

#VALUE!

Excel 2007


The code I have so far will fill Column "A" without accounting for duplicates:

Private Sub cmdTestRank_Click()
Dim LR As Long
LR = Cells(Rows.Count, 3).End(xlUp).Row 'find last row used
MsgBox (LR) 'test msgbox purpose to verify last row- DELETE later.
 
With Worksheets("Sheet2")
    .Range("A2").Value = "1"
    .Range("A2").Select
End With
ActiveCell.Offset(1, 0).Resize(LR - 2, 1).FormulaR1C1 = "=R[-1]C+1"
End Sub

Any Help would be greatly appreciated.

 

[Arul.rajesh]

Have you tried the "RANK" function?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG21Sep15
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            .Add Dn.Value, n
            Dn.Offset(, -2) = n
        [COLOR="Navy"]Else[/COLOR]
            Dn.Offset(, -2) = .Item(Dn.Value)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[blbat]

Thank you both very much for the replies.

Arul.rajesh- I tried =RANK(C2, $C$2:$C$29, 1) and it accounts for the duplicates BUT it does not increment the following cell the way I want...(i.e., Rows 7 and 8 are marked with value of "6", but row 9 is then marked with value of "8" instead of "7")


Mick- this works great, now I have to figure out WHY it works great so I learn.

I figure a google on "scripting.dictionary" is in my future!

 

[Arul.rajesh]

In "A2" put 1

and in A3 put

"=IF(C3=C2,A2,A2+1)"

and drag it down this will work if you say your data is always sorted smallest to largest.

Now for that CreateObject("scripting.dictionary")

 

[Biz]

Try this:-

[COLOR=navy]Sub[/COLOR] MG21Sep15
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
            n = n + 1
            .Add Dn.Value, n
            Dn.Offset(, -2) = n
        [COLOR=navy]Else[/COLOR]
            Dn.Offset(, -2) = .Item(Dn.Value)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR]
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Nice code.

Biz

 

[blbat]

Arul.rajesh- That is a clean bit of formula writing!! it embarasses me that I did not consider such a straightforward formula.

However, the code that Mick wrote I attached to a command button that remains on the sheet...it's easier to click it than it is to drag and drop a formula.

Thanks again.