Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Alphabetical sort of burial book

    I wish to create a searchable A - Z list from a transcribed book containing 500 entries as follows:
    (orig. transcribed to a word doc, now also in Excel 2007)

    1890 Jan 8th To a coffin for Mrs Jane Burlace Died 8th Jan aged 73 years`

    1890 Jan 24th To a coffin for the late Mr James Dunstone aged 82 years

    1890 Feb 12th To a coffin for the late Mrs Emma Hutchings aged 35 years

    1890 Feb 17th To a coffin for the late Mrs N Mills aged 52 years.

    1890 Feb 22nd To a coffin for the late Mrs Jane Johns who died in

    1890 Feb 28th To a coffin for the late Mr Thos. Davey aged 60 years

    1890 Mch 3rd To a coffin for the late Mr Josh Nickells (Uncle Joe) aged 81 years

    1890 Mch 20th To a Coffin for the late Mr Chas. Spicer aged 38 years

    1890 Mch 29 To a Coffin for the late William Stephens Burlace, Boatbuilder,

    Although not shown above, the christian ans surnames are in BOLD

    So my question is, how can a 'sort alphabetically by name' facility be created?

    Alternatively is there a dedicated search engine I can apply to this one file when up loaded to webpage?

    Hoping to hear, thanks in anticipation CB

  2. #2
    New Lounger
    Join Date
    Dec 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    oops NB date are in one column, remaining text, name, age job in another column sorry CB

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Cawsandboy,

    Below is some code that will extract the names from the sentence strings based on their names being bolded. It will then sort the strings by last name

    HTH,
    Maud

    graveyard1.png graveyard2.png

    Code:
    Public Sub SortNames()
    '------------------------------
    'DECLARE AND SET VARIABLES
    Application.ScreenUpdating = False
    Dim s As Variant
    Dim t() As Variant
    Lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    For I = 1 To Lastrow
        If Cells(I, 2) = "" Then GoTo skip
        '------------------------------
        'SPLIT SENTANCE TO ADJACENT CELLS
            s = Split(Cells(I, 2), " ")
            For J = 0 To UBound(s)
                Cells(I, J + 3) = s(J)
            Next J
        '------------------------------
        'LOOK FOR BOLDED WORDS AND ASSIGN BOLD ADJACENT CELLS
            bspace = 1
            For K = 1 To Len(Cells(I, 1))
                If Mid(Cells(I, 2), K, 1) = " " Then
                    bspace = bspace + 1
                    Debug.Print bspace
                    With Cells(I, 2).Characters(Start:=K - 1, Length:=1).Font
                    If .Bold = True Then
                        Cells(I, bspace + 1).Font.Bold = True
                    End If
                    End With
                End If
            Next K
        '-------------------------------
        'GET FIRST AND LAST NAME FROM BOLDED CELLS
            n = 0
            Lastcol = ActiveSheet.Cells(I, Application.Columns.Count).End(xlToLeft).Column
            For M = 3 To Lastcol
                If Cells(I, M).Font.Bold = True Then
                    ReDim Preserve t(n)
                    t(n) = Cells(I, M).Value
                    n = n + 1
                End If
            Next M
        '------------------------------
        'BUILD NAME IN COLUMN A
            Cells(I, 1) = t(UBound(t)) & ", "
            For L = 0 To UBound(t) - 1
                Cells(I, 1) = Cells(I, 1) & t(L)
            Next L
    skip:
    Next I
    '----------------------------------
    'SORT BY NAME IN COLUMN 1
        Range("A2:AZ" & Lastrow).Select
    
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range( _
            "A2:A" & Lastrow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveSheet.Sort
            .SetRange Range("A2:AZ" & Lastrow)
            .Header = xlNo
            .Apply
        End With
    '----------------------------------
    'CLEANUP
        Range("C2:AZ" & Lastrow).ClearContents
        [a1].Select
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-12-21 at 16:50. Reason: Revised book to reflect poster change that date is in separate column. Images do not reflect this

  4. #4
    New Lounger
    Join Date
    Dec 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maudibe, thanks for your solution which worked just great on small selection of entries.

    however when I tried to use on over 1500 lines of entries (half of the book) I got 'cant execute in break mode' with the following highlighted:

    Next M
    '------------------------------
    'BUILD NAME IN COLUMN A
    Cells(I, 1) = t(UBound(t)) & ", "

    For L = 0 To UBound(t) - 1
    Cells(I, 1) = Cells(I, 1) & t(L)
    Next L

    Sorry have no idea what this means or how to cure? Care to help the simple-minded?

    thanks CB

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Is there a reason you are running the code in "Break Mode" instead of just running it?

    Steve

  6. #6
    New Lounger
    Join Date
    Dec 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there, thanks again for your reply. I ran your file and enabled the macro which had the desired effect of sorting the entries.

    I then deleted all prev records and cut and pasted all my records requiring sorting into that file in the hope that the macro would sort the lot.

    When I tested by clicking on the macro cell I got the message concerning 'break mode'?

    Was I stupid???

    Hope to be enlightened
    CB

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    "Break Mode" suggests to me taht you are trying to step through the code and not run it directly.

    If that is not the issue could you attach a sample file that demonstrates the problem?

    Steve

  8. #8
    New Lounger
    Join Date
    Dec 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve thanks for your comments. Attached is my file with data pasted into Maudibe's specimen file that she posted for me.

    Bottom line is I would like an alphabetical index file created from the embolden text - which are the names of the buried and were originally recorded chronologically. With date of burial, someone could then go to the Chron. file and pull up full details of the burial.

    I did notice in Maudibe's code that an entry with 2 forenames did not sort (see last line of sorted entries - Willm Stephens Burlace??

    Grateful for any help you can give

    cheers CB
    Attached Files Attached Files

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    cawsandboy,

    The reason you are getting an error is because your lines and columns are in a different format from what I initially wrote the code for. Could you please post a sample of your data with the format it is in (prior to running code) and I will be happy to adjust the code.

    Maud

  10. #10
    New Lounger
    Join Date
    Dec 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there Maudibe thanks again for your time and help. I did post my entire file in my last posting (which was in response to to Steve's very kind support.) Care to intercept that message (1.1.2014). That file has been reduced to 2 columns of info for simplicity.

    You might be interested in what I am involved with - local history in SE Cornwall in the UK pse go to www.ramehistorygroup.org.uk

    Best wishes for the new year

    RogerC

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't get the Break Mode error when I try the code. I get other errors due to the lack of because as Maud indicated the file format has changed (but he also indicated that he is working on that. There are also variable not defined errors [I always use "Option Explicit" which I highly recommend] and those would have to be DIMmed.

    A further comment, if you are going to be sorting by the date, you may want to keep the year, month and day as separate columns. Excel can't work with dates that are pre-1900. John Walkenbach has an addin to work with "extended dates" at http://j-walk.com/ss/excel/files/xdate.htm which you may want to look into using as well.

    Steve

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Roger,

    The code expects to see 3 columns then writes the name to column 1 and sorts by name from there. Since your data were in columns A and B instead of B and C, you did not get the expected results. I adjusted the code to fist add a new column A and then it ran fine. I noticed many inconsistencies in the format of the statements. The code assumes that the last bolded word is the last name, however, there are cell values like:

    "Coffin for Lucibelle Bools, (?) 7 weeks (cart 5/-) & fees" where the (?) is bolded and code assumes it is the last name

    There are Lines that are split into two cells from the copy/paste (or what ever method you are using to import the data) instead of being concatenated in same cell
    Cell B1037==> To polished Elm Coffin with Black Regl Furniture lined frilled with (no bold and date is present in adjacent to the left)
    Cell B1038==> side sheets etc for the late John F. B. Jope aged 67 years. (bold name but no date in left adjacent cell)

    These inconsistencies are a small percentage and the code gets you about 97% sorted. The few that have an undetermined format can be sorted manually.
    I did notice that if the bolded name is at the end of the line, adding a trailing space at the very end prior to running the code corrects that line. I started correcting a few.

    In this workbook, there is the updated code and also a routine to reset the sheet

    cemetary1.png

    Best I can do with the limited and inconsistent pattern

    HTH,
    Maud
    Attached Files Attached Files

  13. #13
    New Lounger
    Join Date
    Dec 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maud, Hi been away and returned to your solution and patient explanation for which many thanks. Will now amend entries to one line of text against dates with emboldened names to allow full sorting.

    Thanks again RogerC

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Good luck. Let me know if you need any additional help.

    Maud

  15. #15
    New Lounger
    Join Date
    Dec 2013
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there Maud seem to be in a cyclic loop?
    Have changed all entries to one line in column 2 on sheet 2
    moved all data from sheet 1 to sheet 3 to 'save'
    inserted 'one line data' in columns 2 and 3 of sheet 1 with hope that macro would work.
    Now get message 'reopen worksheet and enable macros'
    when I attempt to open macro from view tab I get the same message?

    sorry to be a pain but I must be missing something??

    File attached

    Hope you can help?

    very best wishes

    rogerC
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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