locked
Check if the column in the workseet is sorter in Descending order, if fails return that row. RRS feed

  • Question

  • Hi All,

    I am trying to get the code in VBA to check if the particular column in the sheet (tab) is sorter in descending order? If yes, then insert a row into a SQL table saying it is success, else insert a row in the same table with result as failure with the row number where it got failed on the first occurrence on the sheet.

    Table structure:

    CheckResults (CheckNo, Result, RowID)

    Can someone help me getting the VBA code for this?


    Rahul11


    • Edited by Rahul 11 Wednesday, August 16, 2017 6:53 PM
    Wednesday, August 16, 2017 6:52 PM

Answers

  • Rahul
    re: is dated sorted?

    I can't help you with SQL, but if you have data in a single column in an Excel worksheet...
    The following VBA code will check the data sequence and return the row number where the  sequence check fails.
    NOTE: the failure row in the data range and the worksheet row number will only be the same if the data starts in the first row of the worksheet.
    '---
    Sub CheckDescendingSort()
      Dim N As Long
      Dim rng As Range
      Set rng = Range("H4:H20").Cells             '<<< you must specify the correct range
      For N = 2 To rng.Rows.Count
        If rng(N).Value > rng(N - 1).Value Then
         'insert failure message into SQL table using
         'the numberic N value as the row in the data.
         'N is the row in the data range, not the row in the worksheet.
          MsgBox "Failure at row " & N            '<<< used for testing
          Exit Sub
        End If
      Next
      'insert success message into the SQL table
       MsgBox "Data sorted descending"              '<<< used for testing
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Marked as answer by Rahul 11 Monday, August 21, 2017 7:27 PM
    Wednesday, August 16, 2017 11:08 PM

All replies

  • Rahul
    re: is dated sorted?

    I can't help you with SQL, but if you have data in a single column in an Excel worksheet...
    The following VBA code will check the data sequence and return the row number where the  sequence check fails.
    NOTE: the failure row in the data range and the worksheet row number will only be the same if the data starts in the first row of the worksheet.
    '---
    Sub CheckDescendingSort()
      Dim N As Long
      Dim rng As Range
      Set rng = Range("H4:H20").Cells             '<<< you must specify the correct range
      For N = 2 To rng.Rows.Count
        If rng(N).Value > rng(N - 1).Value Then
         'insert failure message into SQL table using
         'the numberic N value as the row in the data.
         'N is the row in the data range, not the row in the worksheet.
          MsgBox "Failure at row " & N            '<<< used for testing
          Exit Sub
        End If
      Next
      'insert success message into the SQL table
       MsgBox "Data sorted descending"              '<<< used for testing
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Marked as answer by Rahul 11 Monday, August 21, 2017 7:27 PM
    Wednesday, August 16, 2017 11:08 PM
  • This does works for me Jim, but there is actually more to it.

    So, I need to check if the column is sorted in descending order based on the ID column for that particular column.

    For example, I need to check if the Column [Total] is sorted in descending order based on the ID column of it. Here is the sample data look like,

    ID           Total

    1             120

    1               23

    1               12

    2              200

    2              121

    2                26

    So, as shown above the [Total] column should be checked for the sorting and it is tied on the ID column. We need to check if the ID is same then do the SortScheck else go to next ID and do the check. And finally, if we find any failure then pull back that [ID] value.

    Sub CheckDescendingSort()
       Dim N As Long
       Dim rng As Range
       Dim ws As Worksheet
       
       Set ws = ThisWorkbook.Worksheets("1asheet")
       Set rng = ws.Range("S6:S2000").Cells
       
       For N = 2 To rng.Rows.Count
         If rng(N).value > rng(N - 1).value Then
          'insert failure message into SQL table using
          'the numberic N value as the row in the data.
          'N is the row in the data range, not the row in the worksheet.
           MsgBox "Failure at row " & N
           Exit Sub
         End If
       Next
       'insert success message into the SQL table
        MsgBox "Data sorted descending"              '<<< used for testing
     End Sub

    Can you help me in this?


    Rahul11


    • Edited by Rahul 11 Thursday, August 17, 2017 2:14 PM
    Thursday, August 17, 2017 2:14 PM
  • Rahul,
    re:  sort problems

    1.  What/where/who is doing the existing sort?
    2.  Is the ID data strictly numeric  and do the numbers align to the right in each cell?
    3.  Same question for the "total" data.
    3.  Has the data (all columns) been cleaned? ...
            remove extra spaces, move trailing minus signs to the front, unmerge cells,
            remove non-printing characters and word wrap,
            replace carriage returns and non-breaking spaces with a space

    A standard Excel sort will do what you want (ID column sorted ascending, Total column sorted descending within each ID group); but it depends on what you present to Excel.
    '---
    Jim Cone

    Thursday, August 17, 2017 3:14 PM
  • re:  sort problems - more info

    The point is one cannot determine if a sort is correct if the data is messy.
    Excel sorts text separately from numbers. So...
     1234 with a leading space sort sorts separately from 1234 without a leading space.
    The first one is text and second is a number.
    Thursday, August 17, 2017 4:10 PM