#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    52
    Rep Power
    9

    VBA sort question in Excel


    Hi, I hope this is the right place for this, if not, please let me know where I should post instead.

    I have a range consisting of three columns which I want to sort.
    The first column contains a description of a task and is always filled in.
    The second contains a due date for the tasks' completion and is sometimes blank.
    The third contains text saying "complete" if the task has been completed, "n/a" if it is no longer applicable and is blank if the task is yet to be completed.

    I want to sort all of these columns together based on the following rules:
    at the top should be all tasks for which column 3 is blank and these should in data order given by column 2 with the nearest date first.
    below these should be any tasks for which column 3 is blank and column 2 is blank (i.e. no due date is given)
    below these should be any tasks with "complete" in column 3
    at the bottom should be any tasks with "n/a" in column 3

    I managed to sort all the columns according to date order using the following code:
    Code:
    Option Explicit
    
    Sub sortByDate()
    
    Dim allData As Range, numRows As Long
    
        numRows = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
        
        Set allData = ActiveSheet.Range("a2:c" & numRows)
        
        allData.Sort key1:=ActiveSheet.Range("b2:b" & numRows), order1:=xlAscending
    
    End Sub
    which works. However I have failed to achieve any of the other sorting operations. I have tried using more keys but don't get the results I want, i.e. those described above.

    I'd really appreciate any help that anyone could offer.
    Many thanks!
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2010
    Posts
    8
    Rep Power
    0
    Originally Posted by devshed-rob
    Hi, I hope this is the right place for this, if not, please let me know where I should post instead.

    I have a range consisting of three columns which I want to sort.
    The first column contains a description of a task and is always filled in.
    The second contains a due date for the tasks' completion and is sometimes blank.
    The third contains text saying "complete" if the task has been completed, "n/a" if it is no longer applicable and is blank if the task is yet to be completed.

    I want to sort all of these columns together based on the following rules:
    at the top should be all tasks for which column 3 is blank and these should in data order given by column 2 with the nearest date first.
    below these should be any tasks for which column 3 is blank and column 2 is blank (i.e. no due date is given)
    below these should be any tasks with "complete" in column 3
    at the bottom should be any tasks with "n/a" in column 3

    I managed to sort all the columns according to date order using the following code:
    Code:
    Option Explicit
    
    Sub sortByDate()
    
    Dim allData As Range, numRows As Long
    
        numRows = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
        
        Set allData = ActiveSheet.Range("a2:c" & numRows)
        
        allData.Sort key1:=ActiveSheet.Range("b2:b" & numRows), order1:=xlAscending
    
    End Sub
    which works. However I have failed to achieve any of the other sorting operations. I have tried using more keys but don't get the results I want, i.e. those described above.

    I'd really appreciate any help that anyone could offer.
    Many thanks!





    Here is a sorting Algorithm written in VBA.
    It is a good place to start as your sorting functionality will require more than just the basic Excel script.





    Public Sub QuickSortVariants(vArray As Variant, inLow As Long, inHi As Long)

    'vArray() The array to sort
    'inLow Lower bound of sort point
    'inHi Upper bound of sort point

    'Dim two working variables to hold
    'array members. The first holds the
    'pivot item - the item half way between
    'the inLow and inHi values, while the
    'second is used to hold the array contents
    'that will be swapped later.
    'These two items should be declared as
    'the same data type as the array passed.
    Dim pivot As Variant
    Dim tmpSwap As Variant

    'Dim two working variables to hold the
    'values representing the pivot's lower
    'and upper points as passed to the routine.
    'These should be declared as the same data
    'type as the inLow/inHi variables passed.
    Dim tmpLow As Long
    Dim tmpHi As Long

    'Save to the working variables the
    'values passed as lower & upper
    tmpLow = inLow
    tmpHi = inHi

    'Get the item halfway through the data
    'determined by the range passed as lower
    'and upper and assign it as the pivot data
    '
    'When first calling this routine the
    'range is inLow = LBound(array), and
    'inHi = UBound(array). During subsequent
    'calls, inLow and inHi will receive
    'different values as determined below.
    pivot = vArray((inLow + inHi) 2)

    'With pivot holding the value of the item
    'halfway through the range, compare the
    'rank of tmpLow to tmpHi and assign the two
    'tmp storage variables that data for later
    'comparison. Here we're continuing the loop
    'while the low item being compared value (tmpLow)
    'is less than tmpHi (the upper bound value).
    While (tmpLow <= tmpHi)

    'Since (and while) tmpLow remains less than
    'tmpHi, compare the value of the array()
    'element at this position against pivot.
    While (vArray(tmpLow) < pivot And tmpLow < inHi)
    tmpLow = tmpLow + 1
    Wend

    'repeat the same for the array value at
    'position tmpHi.
    While (pivot < vArray(tmpHi) And tmpHi > inLow)
    tmpHi = tmpHi - 1
    Wend

    'When the position of tmpHi exceeds or matches tmpLow
    'swap the two items.
    If (tmpLow <= tmpHi) Then

    'a: assign vArray(tmpLow) to tmpSwap
    'b: swap vArray(tmpHi) for vArray(tmpLow)
    'c: assign tmpSwap back to vArray(tmpHi)
    tmpSwap = vArray(tmpLow)
    vArray(tmpLow) = vArray(tmpHi)
    vArray(tmpHi) = tmpSwap

    'adjust the new Hi and Low values
    tmpLow = tmpLow + 1
    tmpHi = tmpHi - 1
    End If

    Wend

    'if the original lower is less than tmpHi,
    'call the routine again with inLow & tmpHi
    'as the pivot's lower and upper points.
    If (inLow < tmpHi) Then QuickSortVariants vArray, inLow, tmpHi

    'if the new tmpLow value lower is less than
    'the original inHi, call the routine again with
    'tmpLow & inHi as the pivot's lower and upper points.
    If (tmpLow < inHi) Then QuickSortVariants vArray, tmpLow, inHi

    End Sub







    Here is the C code for a quick sort:
    void quicksort(Item a[], int l, int r)
    { int i = l-1, j = r, p = l-1, q = r; Item v = a[r];
    if (r <= l) return;
    {
    while (a[++i] < v) ;
    while (v < a[--j]) if (j == l) break;
    if (i >= j) break;
    exch(a[i], a[j]);
    if (a[i] == v) { p++; exch(a[p], a[i]); }
    if (v == a[j]) { q--; exch(a[j], a[q]); }
    }
    exch(a[i], a[r]); j = i-1; i = i+1;
    for (k = l; k < p; k++, j--) exch(a[k], a[j]);
    for (k = r-1; k > q; k--, i++) exch(a[i], a[k]);
    quicksort(a, l, j);
    quicksort(a, i, r);
    }
  4. #3
  5. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,883
    Rep Power
    889
    Excel sort should work fine, his problem isn't with the sort it's the specific way he needs things sorted and any canned sort will work essentially the same as far as sorts go. What I'd do in this scenario is build a temporary sort column then sort on that which considers all the various conditions he's got in his logic. I was going to write something up detailing that but I frankly don't have the time to dedicate to that.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    52
    Rep Power
    9
    Originally Posted by medialint
    Excel sort should work fine, his problem isn't with the sort it's the specific way he needs things sorted and any canned sort will work essentially the same as far as sorts go. What I'd do in this scenario is build a temporary sort column then sort on that which considers all the various conditions he's got in his logic. I was going to write something up detailing that but I frankly don't have the time to dedicate to that.
    Massive delay in my response as I was taken off working on this, but back on it now.

    I don't want you to provide the solution, but could you give me a bit more of a hint as to how to sort the temporary column on all those conditions, that is the bit I'm struggling with!

    Not really done any work on sorting routines before so any help would be greatly appreciated.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Location
    London
    Posts
    96
    Rep Power
    0
    I suggest you use the "Help" option on the top menu of Excel.

    Comments on this post

    • medialint disagrees : I suggest you not bother replying to posts if you can't grok the question and have nothing useful to contribute
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    126
    Rep Power
    161

    dont look at the data look at your output


    You have said you dont want us to write it for you so I am thinking you want to know the secrets of advanced pattern matching and ordinality to improve your programming skills.

    To that end please read the following as a quicky lesson on advanced sorting through appropriate data flagging.

    You appear to have missed a bit from your specification, simply saying then x followed by y after a lengthy description of column based ordering does not inform the programmer of the correct order of last section in your problem. This aside you have missed a simple approach to this problem, that of implied ordinality. You should always be able to generate an ordinal value for any member of a dataset that you want to order. If the value or values you are using are incomplete or otherwise incompatable or traditionally tend towards a different order, then you must inforce an order on it/them by introducing an ordinal value that expresses you desired order. The brain bending bit is to recognise the pattern in the mess you want to order and create data that turns in your favour.

    Hint for your problem - generate a complex_data_item that draws from the data provided and fill in the blanks with data that aids ordering - i.e. where no date exists use a date that is before or after the dated entries so that it can be ordered, where textual data is to be ordered add an ordinal element to the complex_data_item that reflects the ordinal nature of said text and finally add weight to the sections you wish to order so that the sectional data clumps in an appropriate place in your order.

    Sorry for the long drawn out lenguage , but trying to encapuslate the general in this answer.

    if you need more please ask.

    (I have a complete description of how to create the column you require for this sorting problem, its really simple once you think it through... happy to post/email it if req.)

    Comments on this post

    • medialint agrees
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    52
    Rep Power
    9
    This must be one of the longest running threads in history, due to my inability to spend enough time on it! Sorry about that.

    I keep coming back to your description which is, as you say, written in very general terms, unfortunately making it impossible for me to grasp! Could you be more specific?

    Thanks for your help.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    126
    Rep Power
    161
    Originally Posted by devshed-rob
    This must be one of the longest running threads in history, due to my inability to spend enough time on it! Sorry about that.

    I keep coming back to your description which is, as you say, written in very general terms, unfortunately making it impossible for me to grasp! Could you be more specific?

    Thanks for your help.
    I have been and sent him a great big explaination of how to what medialint and myself have suggested to him. I hope he uses the idea to his advantage and reports back her (job done)

    Comments on this post

    • medialint agrees : This is the Q/A that deserved the rep lob I lobbed earlier ;-)
    • devshed-rob agrees : Fantastically helpful over a very long period!
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    52
    Rep Power
    9

    Solved


    Originally Posted by Incidentals
    I have been and sent him a great big explaination of how to what medialint and myself have suggested to him. I hope he uses the idea to his advantage and reports back her (job done)
    Many thanks for your help and everyone else's on this. With the example I was able to understand very quickly and have implemented it already. I hadn't picked out that the status column ("completed", "n/a" or blank) should be the ordinal group and the date subordinal, as you said, that is the brain bending bit!

IMN logo majestic logo threadwatch logo seochat tools logo