Hi all,The code below sorts the figures out in each block of rows then places the position of that figure in the adjacent column,like this where the smallest number is 1 second smallest 2 and so on.
CI CJ
1 24
2 26
3 29
4 34
5 35
6 37


Sub rankandSort()
   
   Dim Rng As Range
   Dim Ar As Areas
   
   Set Ar = Range("A2", Range("I" & Rows.Count).End(xlDown)).SpecialCells(xlConstants).Areas
   Columns(9).Insert
   Range("I1").Value = "Position"
   
   For Each Rng In Ar
      Rng.Sort key1:=Range("J:J"), order1:=xlAscending 'added was :=xlAscending
      With Intersect(Rng, Range("I:I"))
         .FormulaArray = "=rank(" & .Offset(, 1).Address & "," & .Offset(, 1).Address & ",1)"
         .Value = .Value
      End With
   Next Rng
End Sub
the problem arises when there are identical numbers(two or more in col j).In the following eg there are three identical
numbers in3, 4 and 5.And the code does a good job of placing them as all 3rd in position,but then instead of the final cell(184) being 4th it presents as 6th.Likewise if cells 1,2,3 were identical cell 4 would then present itself as 4th as opposed to 2nd,and so on.
1 143.0
2 144.0
3 145.0
3 145.0
3 145.0
6 184.0
does anyone have any suggestion as to modify the code to ensure that the cells values in the J cells are correctly reflected in th I cells adjacent
Kind RegardsBIF