Announcement

Collapse
No announcement yet.

sort works but accompanying value column doesn't correspond

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • sort works but accompanying value column doesn't correspond



    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
    Code:
    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 Regards
    TT

  • #2
    It's a feature of the inbuilt RANK function. To get what you need, you could use a calculated rank formula instead.
    Assuming row 1 contains headers and your values (143.0, 144.0 etc) are in column I, the rank formula for cell J2 would be:
    =IF(ROW()=2,1,IF(I2=I1,J1,J1+1))
    and this can be copied down to the rest of column J
    You just need to create the formula in VBA, copy it down to the end of the column and then copy/paste back just the values (if you don't want formulas in the column)

    Simplistically, using columns I & J, code could look as follows:
    Code:
      Dim lLast As Long
     
      'set up the formula for first row
      Range("J2").Formula = "=IF(ROW()=2,1,IF(I2=I1,J1,J1+1))"
      'calc the last row, 9 is column I
      lLast = Cells(Rows.Count, 9).End(xlUp).Row
     
      'copy the formulas and calculate, then paste back values
      With Range("J2", Range("J" & lLast))
        .FillDown
        .Calculate
        .Copy
        .PasteSpecial Paste:=xlPasteValues
      End With

    Comment


    • #3
      Hi gijsmo,
      many thanks for that and the code and works great for the first block of rows,but doesnt work on the rest of the blocks of rows(usually either six or under).Is there a way i could loop your code through to the other blocks also?.
      Kind Regards TT

      Comment


      • #4
        OK, the code snippet was based on your original post and I've just realised that my code has swapped your Column I (rank) with Column J (value).
        Therefore the code snippet to match your example should be:
        Code:
        Dim lLast As Long
         
          'set up the formula for first row
          Range("I2").Formula = "=IF(ROW()=2,1,IF(J2=J1,I1,I1+1))"
          'calc the last row, 10 is column J
          lLast = Cells(Rows.Count, 10).End(xlUp).Row
         
          'copy the formulas and calculate, then paste back values
          With Range("I2", Range("I" & lLast))
            .FillDown
            .Calculate
            .Copy
            .PasteSpecial Paste:=xlPasteValues
          End With
        If there is data in 'other blocks' as you suggest, then the best thing to do is post an example sheet (without any sensitive data) to see what you are referring to.
        At the moment, the (revised) code assumes there is contiguous data in Column J and a corresponding rank is calculated in Column I.

        Comment


        • #5
          Hi gijsmo
          Thanks for the code and it works fine on the first block of rows(6 rows in this block put it can be 6,5or 4)But as you pointed out the rows are not contiguous,between each block of rows there is one empty row.My original code above loops through the blocks of rows and performs a sort on the perentage column,I then run your code and that rectifies the positioning problem.But your code at the moment is just working on the first block of rows.I have been trying to create a loop to incorporate your code but have not cracked it yet.
          I am putting a sample sheet together to post up here.Thanks
          Kind RegardsTT

          Comment


          • #6


            Best to also include an example of what the expected output will be ie, does the rank restart at 1 after a blank row or does it continue on from the end of the previous block.

            Comment

            Working...
            X