Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Smile Sorting Verses Using VBA

    Hi Experts,

    I would like to sort a list of scriptures from the Book of Mormon.

    I thought I could do this my using Excels built in Custom Sort. It didn't work out the way I thought it would.

    The Book of Mormon has the following books in order as they appear in table of contents:

    "1 Nephi, 2 Nephi, Jacob, Enos, Jarom, Omni, Words of Mormon, Mosiah, Alma, Helaman, 3 Nephi, 4 Nephi, Mormon, Ether, Moroni"

    Here is the code Excel produced. The problem is that it didn't keep the books in order. I worked with the code but couldn't find a way to keep it from being alphabetized.



    Range("A1:A63").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("A1:A63") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
    "1 Nephi,2 Nephi,Jacob,Enos,Jarom,Omni,Words of Mormon,Mosiah,Alma,Helaman,3 Nephi,4 Nephi,Mormon,Ether,Moroni" _
    , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:A63")
    .Header = xlGuess
    .MatchCase = True
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    End Sub



    Attached is a file with the verses of scriptures I would like to put in order by book. That is, order the verses by each book and keep the books in order as shown in CustomOrder

    Thanks for any help.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    E.N.,

    This problem has to do with the way alphanumeric sorts are performed. The short story is it won't work with the Books & Verses in the same column.

    However if you separate them out it works just fine.

    Here's your code simplified:
    Code:
    Sub SortBoM()
    
        Range("A1").Select
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A1:A63") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
            "1 Nephi,2 Nephi,Jacob,Enos,Jarom,Omni,Words of Mormon,Mosiah,Alma,Helaman,3 Nephi,4 Nephi,Mormon,Ether,Moroni" _
            , DataOption:=xlSortNormal
            .SetRange Range("A1:B63")
            .Header = xlGuess
            .MatchCase = True
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
    End Sub
    Here's the code I used to separate the Books & Verses into separate columns.
    Code:
    Sub BreakOut()
    
       Dim lNextRow  As Long
       Dim vParts    As Variant
       Dim iCntr     As Integer
       
       lNextRow = 1
    
       Do
         vParts = Split(Cells(lNextRow, 1), " ")
         For iCntr = 0 To UBound(vParts) - 1
            Cells(lNextRow, 2) = Cells(lNextRow, 2) & IIf(iCntr = 1, " ", "") & vParts(iCntr)
         Next iCntr
         Cells(lNextRow, 3).Value = vParts(UBound(vParts))
         lNextRow = lNextRow + 1
       Loop Until Cells(lNextRow, 1) = ""
       
    End Sub
    Note: for the above code to work I had to do a global search and replace to remove the space after the comma for the citations with multiple verses.
    ensplit.PNG
    Now delete Col A and then perform the sort on the new column A with just the Book names.
    ensorted.PNG
    Note: I hid all the repeating book entries leaving only the first and last to save display space.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-04-18)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Excel,

    Here is a custom sort for the order of the books you specified. Highlight the cells in the column as you normally would with a sort then run the code. The books will be sorted in column B then column A is removed.

    HTH,
    Maud

    Code:
    Public Sub SortScripture()
    '----------------------------------
    'DECLARE AND SET VARIABLES
    Dim Scripture, Verses(15), cell As Range
    Scripture = Array("1 Nephi", "2 Nephi", "Jacob", "Enos", "Jarom", "Omni", "Words of Mormon", "Mosiah", "Alma", "Helaman", "3 Nephi", "4 Nephi", "Mormon", "Ether", "Moroni")
    Row = 1
    Application.ScreenUpdating = False
    '----------------------------------
    'SORT ACCORDING TO SCRIPTURE ARRAY
    For I = 0 To 14
        count = 0
        For Each cell In Selection
            If InStr(1, cell, Scripture(I), vbTextCompare) > 0 Then
                count = count + 1
                Cells(Row, 2) = cell
                Row = Row + 1
            End If
        Next cell
        Verses(I) = count
        'Debug.Print Scripture(I); Verses(I)
    Next I
    '----------------------------------
    'SORT WITHIN EACH BOOK
    Index = 1
    For I = 0 To 14
        If Verses(I) = 0 Then GoTo NoVerses
        ActiveSheet.Sort.SortFields.Clear
         ActiveSheet.Sort.SortFields.Add Key:=Range("B" & Index & ":B" & Index + Verses(I) - 1)
        With ActiveSheet.Sort
            .SetRange Range("B" & Index & ":B" & Index + Verses(I) - 1)
            .Apply
        End With
        Index = Index + Verses(I)
    NoVerses:
    Next I
    '----------------------------------
    'REMOVE COLUMN A
    Columns("A:A").Delete Shift:=xlToLeft
    '----------------------------------
    Application.ScreenUpdating = True
    End Sub

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2016-04-18)

  6. #4
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    RG and Maud,

    Thank you so much. It is a learning experience to be taught by the two of you.

    I will study the code you provided and try to make it my own.

    Now that the books are in order, the next step will be to find a way to put the chapters (but not the verses) in order.

    Once again, thanks to both of you for being great teachers.

  7. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    RG and Maud,

    Is there a way to modify the code you so kindly provided so that it sorts the chapters (not the verses).

    For example, in the book of Alma the chapters are out of order. It appears the code provided is "looking" at the chapters first digit only.

    Currently it appears this way:

    Alma 18:5
    Alma 19:16
    Alma 35:11
    Alma 38:14
    Alma 4:17
    Alma 45:10-11
    Alma 45:18-19
    Alma 46:13
    Alma 46:3-4
    Alma 46:8
    Alma 48:11-13
    Alma 51:5
    Alma 8:17

    I would like it to look this way:

    Alma 4:17
    Alma 8:17
    Alma 18:5
    Alma 19:16
    Alma 35:11
    Alma 38:14
    Alma 45:10-11
    Alma 45:18-19
    Alma 46:13
    Alma 46:3-4
    Alma 46:8
    Alma 48:11-13
    Alma 51:5


    In my hours of research with Prof Google it didn't have anything that helped.

    PS I did find some code that might be useful to you or others. It is attached.
    Attached Files Attached Files
    Last edited by Excelnewbie; 2016-04-21 at 16:57.

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    E.N.,

    IMHO this one will take an "Act of God" to accomplish due to the "Devilish" inconsistency in your verse notation! :lol:

    Examples:
    Code:
    Alma 4:17            Single Ch/Vers no problemo!
    Alma 15:6-11         Single Ch/Vers range (converts to date)
    Helaman 4:13,15      Single Ch/Skipped verse (converts to text if it can't make a date of it)
    Nephi 1-7            Chapter range no verses! (converts to date or text as above)
    Healman 8:27,9:25    Multiple Chapters can't parse properly.
    Trying to tear these apart is a nightmare. Data works best when it is highly structured.

    You tend to get gibberish (at least as far as Sort is concerned)

    Verses.PNG

    Breaks down quickly as shown in the highlighted line.

    Structure.PNG

    Sorting requires Structure with out it you have a pretty impossible task.

    Maybe someone with a more organized mind can find what I'm missing? Maud, Zeddy you guys listening? :lol:

    :cheers:
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    RG,

    lol. Let's look at just the book and chapter:

    Alma 4:
    Alma 15:
    Nephi 1-7 is for chapters only and the 1 is all that needs to be considered
    Helaman 4:
    Helaman 8:

    Thanks again

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    E.N.,

    Ok, here's the revised code that only sorts on Book (custom order) and Chapter!

    I've incorporated a call to the Sort in the BreakOut code along with the automatic deletion of the helper columns used to do the sort so you end up with a single column as you started with.

    Code:
    Option Explicit
    
    Sub SortBoM()
    
        Range("B1").Select
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("B1:B63") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
            "1 Nephi,2 Nephi,Jacob,Enos,Jarom,Omni,Words of Mormon,Mosiah,Alma,Helaman,3 Nephi,4 Nephi,Mormon,Ether,Moroni" _
            , DataOption:=xlSortNormal
            .SortFields.Add Key:=Range("C1:C63") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:C63")
            .Header = xlGuess
            .MatchCase = True
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
    End Sub
    
    Sub BreakOut()
    
       Dim lNextRow  As Long
       Dim vParts    As Variant
       Dim vChVers   As Variant
       Dim iCntr     As Integer
       
       Application.ScreenUpdating = False  'Suppress screen flicker
       
       lNextRow = 1
    
       Do
         vParts = Split(Cells(lNextRow, 1), " ")
         For iCntr = 0 To UBound(vParts) - 1
            Cells(lNextRow, 2) = Cells(lNextRow, 2) & IIf(iCntr = 1, " ", "") & vParts(iCntr)
         Next iCntr
    
    '*** The following code handles breaking the Chapter out if it is followed by a Verse indicated by a colon
    '*** or followed by another chapter indicated by a dash!
    
         vChVers = Split(vParts(UBound(vParts)), ":")
         If (UBound(vChVers) = 0) Then
           vChVers = Split(vChVers(0), "-")
         End If
         Cells(lNextRow, 3) = vChVers(0)
         lNextRow = lNextRow + 1
       Loop Until Cells(lNextRow, 1) = ""
       
       SortBoM   'Call the sort
       
       Columns("B:C").EntireColumn.Delete    'Delete the Helper Columns
       
    End Sub
    HTH
    Last edited by RetiredGeek; 2016-04-21 at 21:30.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi

    A simple approach might be to 'make the data sortable, then sort, then put the data back as it was previously'.
    see attached example file:
    Select the range, then click the [sort selected range] button.
    This sorts as per your example requirement in post#5.
    You could use this kind of method to deal with other sorting issues.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2016-04-22 at 06:15.

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by zeddy View Post
    A simple approach might be to 'make the data sortable'
    My point exactly!

    This is an interesting concept but becomes a coding nightmare if you have more than 99 chapters and it breaks down with references like Nephi 1-7.

    ...and the nightmare continues...

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi RG

    Exactly.
    I was trying to give the younger whippersnappers coding nightmares.
    Us older whippersnappers could then show why they shouldn't do that.
    (but we could use loop if there were 1,000 chapters)
    (..or other ways)

    zeddy

  14. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    Oh there are so many comments I could make.....but this is a family site!
    ROTFLOL.gif

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #13
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts
    RG, Maud, and Zeddy,

    Thanks to you this problem is just about resolved. I am working on what you've provided and hope to have it wrapped up before long.

    You're the best!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •