+ Reply to Thread
Results 1 to 6 of 6

Sorting

  1. #1
    Dave Unger
    Guest

    Sorting

    Hi everyone,

    Sorry, I can't seem to get the columns to line up in the post.

    I'm maintaining a family tree with a spreadsheet, basic components
    consist of name and generation index.

    | Grandfather's sibling | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandfather | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Son #1 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 1- 1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 1-2 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
    | Son #2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 2-1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 2-1-1 | 2 | 2 | 1 | 1 | 0 | 0 | 0 | 0 |
    | Grandson 2-2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
    | Grandfather's sibling | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

    etc.

    Sometimes it's handy to have all the siblings in a generation grouped
    together like this:

    | Grandfather's sibling | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandfather | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandfather's sibling | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Son #1 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Son #2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 1-1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 1-2 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 2-1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 2-2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 2-1-1 | 2 | 2 | 1 | 1 | 0 | 0 | 0 | 0 |

    This one has me stumped, and I've got myself in a rut thinking about
    it. As a workaround, I have a routine that builds a secondary index,
    which will produce the desired result using "normal" sorting.
    Basically, it's a copy of the regular index, with all the non-zero
    digits shifted to the extreme right.

    | Grandfather's sibling | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
    | Grandfather | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
    | Son #1 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 1 |
    | Grandson 1- 1 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | 1 |
    | Grandson 1-2 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | 2 |
    | Son #2 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 0 |
    | Grandson 2-1 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 1 |
    | Grandson 2-1-1 | 0 | 0 | 0 | 0 | 2 | 2 | 1 | 1 |
    | Grandson 2-2 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 2 |
    | Grandfather's sibling | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 |

    While this does work, it seems like a clumsy method, as well as adding
    a lot of data to an already large file. One idea I had was to combine
    each persons index into one integer, and shift it with some simple
    math, but the fact that some of the older generations had more than 9
    kids complicated things.

    If anyone has any ideas, I would be glad to hear them.

    Thanks,

    DaveU


  2. #2
    David McRitchie
    Guest

    Re: Sorting

    Hi Dave,
    I don't know if you are showing all of the data necessary
    to answer your question, but from what you have shown.

    for name Generation Index
    sort on Column B, Column C, Column D all ascending

    for generation grouped
    sort on Column C, Column B, Column D all ascending
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "Dave Unger" <[email protected]> wrote in message news:[email protected]...
    > Hi everyone,
    >
    > Sorry, I can't seem to get the columns to line up in the post.
    >
    > I'm maintaining a family tree with a spreadsheet, basic components
    > consist of name and generation index.
    >
    > | Grandfather's sibling | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Grandfather | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Son #1 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 1- 1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 1-2 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
    > | Son #2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 2-1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 2-1-1 | 2 | 2 | 1 | 1 | 0 | 0 | 0 | 0 |
    > | Grandson 2-2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
    > | Grandfather's sibling | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    >
    > etc.
    >
    > Sometimes it's handy to have all the siblings in a generation grouped
    > together like this:
    >
    > | Grandfather's sibling | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Grandfather | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Grandfather's sibling | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Son #1 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Son #2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 1-1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 1-2 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 2-1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 2-2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 2-1-1 | 2 | 2 | 1 | 1 | 0 | 0 | 0 | 0 |
    >
    > This one has me stumped, and I've got myself in a rut thinking about
    > it. As a workaround, I have a routine that builds a secondary index,
    > which will produce the desired result using "normal" sorting.
    > Basically, it's a copy of the regular index, with all the non-zero
    > digits shifted to the extreme right.
    >
    > | Grandfather's sibling | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
    > | Grandfather | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 |
    > | Son #1 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 1 |
    > | Grandson 1- 1 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | 1 |
    > | Grandson 1-2 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | 2 |
    > | Son #2 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 0 |
    > | Grandson 2-1 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 1 |
    > | Grandson 2-1-1 | 0 | 0 | 0 | 0 | 2 | 2 | 1 | 1 |
    > | Grandson 2-2 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 2 |
    > | Grandfather's sibling | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 |
    >
    > While this does work, it seems like a clumsy method, as well as adding
    > a lot of data to an already large file. One idea I had was to combine
    > each persons index into one integer, and shift it with some simple
    > math, but the fact that some of the older generations had more than 9
    > kids complicated things.
    >
    > If anyone has any ideas, I would be glad to hear them.
    >
    > Thanks,
    >
    > DaveU
    >




  3. #3
    Dave Unger
    Guest

    Re: Sorting

    David,

    Thanks for your reply, but that doesn't seem to work for me. I end up
    with this

    | Grandfather's sibling | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandfather | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandfather's sibling | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Son #1 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 1-1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 1-2 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
    | Son #2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 2-1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
    | Great-Grandson 2-1-1 | 2 | 2 | 1 | 1 | 0 | 0 | 0 | 0 |
    | Great-Grandson 2-2-2 | 2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 |

    and I want this

    | Grandfather's sibling | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandfather | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandfather's sibling | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Son #1 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Son #2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 1-1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 1-2 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
    | Grandson 2-1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
    | Great-Grandson 2-1-1 | 2 | 2 | 1 | 1 | 0 | 0 | 0 | 0 |
    | Great-Grandson 2-2-2 | 2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 |

    Here's a real sample of my data. I can get this with no problem:

    | JACK JONES | 6 | 7 | 3 | 12 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | TYLER JONES | 6 | 7 | 3 | 12 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | WILLIAM JONES | 6 | 7 | 3 | 12 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0
    |
    | PETER JONES | 6 | 7 | 3 | 12 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0
    |
    | JOHN JONES | 6 | 7 | 3 | 12 | 1 | 1 | 2 | 0 | 0 | 0 | 0 | 0
    |
    | WILL JONES | 6 | 7 | 3 | 12 | 1 | 2 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | JOHANN JONES | 6 | 7 | 3 | 12 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | WILLIAM JONES | 6 | 7 | 3 | 12 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | JOHAN JONES | 6 | 7 | 3 | 12 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0
    |
    | WILHELM JONES | 6 | 7 | 3 | 12 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0
    |
    | ELIZABETH JONES | 6 | 7 | 3 | 12 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | HELEN JONES | 6 | 7 | 3 | 12 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0
    |

    The difficulty is achieving this (without building an extra index)

    | JACK JONES | 6 | 7 | 3 | 12 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | JOHANN JONES | 6 | 7 | 3 | 12 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | HELEN JONES | 6 | 7 | 3 | 12 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | TYLER JONES | 6 | 7 | 3 | 12 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | WILL JONES | 6 | 7 | 3 | 12 | 1 | 2 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | WILLIAM JONES | 6 | 7 | 3 | 12 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0
    |
    | JOHN JONES | 6 | 7 | 3 | 12 | 1 | 1 | 2 | 0 | 0 | 0 | 0 | 0
    |
    | PETER JONES | 6 | 7 | 3 | 12 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0
    |
    | WILLIAM JONES | 6 | 7 | 3 | 12 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | ELIZABETH JONES | 6 | 7 | 3 | 12 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0
    |
    | JOHAN JONES | 6 | 7 | 3 | 12 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0
    |
    | WILHELM JONES | 6 | 7 | 3 | 12 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0
    |

    The zero's are place holders, they get replaced with > 0 digits as the
    newer generations come along.

    Thanks,

    DaveU


  4. #4
    David McRitchie
    Guest

    Re: Sorting

    Hi Dave,
    Wouldn't it be easier to use a software package like Family Tree Maker
    rather than trying to make up an Excel file, or did you extract your data
    from such a package. Sometime during the year some stores practically
    give you the software for free (except for tax on full price, stamps, time and trouble).

    Why do you think you can make Excel sort in a different manner
    without creating another column for sorting, even if you were
    simply sorting on 12 different columns you can't do it in one pass
    with Excel, and that in itself using 4 different sorts would be
    accomplished better in a macro, which is not a problem because
    you posted in programming.

    Anyway as I see it from your data and strictly from the last two
    examples, you certainly cannot use Excel alone to sort your data. You
    need a macro to sort each column twice by creating a text string
    to sort B you have to check if C has anything above 0, and to
    sort C you have to check if D has anything above zero.
    cBdCeDfEgFhGiHjIkJlKmLM
    the lowercase will be - if a zero, and x if above zero
    the uppercase will be two digit numbers as a string
    the appearance will be something like
    x06x07x03x12x01-02-00-00-00-00-0000
    for Will JONES 6 7 3 12 1 2 0 0


    The macro will not have to sort in groups of threes from
    minor groups to higher order groups because we will sort
    on a single character string, which can be created and
    destroyed (column 14), but I'll leave it in along with a column for the
    original sequence (column 15 which is P).

    Actually you put the SURNAME into a separate column
    and include a spouse, all the more reason to use a package.
    So I won't bother trying to separate out columns.

    You should be able to proceed with the above, but since you
    would probably not post the resulting macro for others, I might as well
    finish it.

    Sub Genealogy_sort_B_M()
    'David McRitchie, 2005-12-20, programming, reply to Dave Unger
    Dim rng As Range, cell As Range, str As String, i As Long
    Set rng = Intersect(Range("A:M"), ActiveSheet.UsedRange)
    For Each cell In Intersect(rng, Range("A:A"))
    cell.Offset(0, 15) = cell.row 'reference current row before sort
    str = "" 'initialize str
    For i = 1 To 11
    If cell.Offset(0, i + 1).Value = 0 Then
    str = str & "-"
    Else
    str = str & "x"
    End If
    str = str & Format(cell.Offset(0, i), "00")
    Next i
    cell.Offset(0, 14) = str & Format(cell.Offset(0, 12), "00")
    Next cell
    Cells.Sort Key1:=Range("O1"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    End Sub

    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "Dave Unger" <dave.unger@ sasktel DoT net> wrote in message
    > I want this
    >
    > | Grandfather's sibling | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Grandfather | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Grandfather's sibling | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Son #1 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Son #2 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 1-1 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 1-2 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0 |
    > | Grandson 2-1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
    > | Great-Grandson 2-1-1 | 2 | 2 | 1 | 1 | 0 | 0 | 0 | 0 |
    > | Great-Grandson 2-2-2 | 2 | 2 | 2 | 2 | 0 | 0 | 0 | 0 |
    >
    > The difficulty is achieving this (without building an extra index)
    > | JACK JONES | 6 | 7 | 3 | 12 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0
    > | JOHANN JONES | 6 | 7 | 3 | 12 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0
    > | HELEN JONES | 6 | 7 | 3 | 12 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0
    > | TYLER JONES | 6 | 7 | 3 | 12 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0
    > | WILL JONES | 6 | 7 | 3 | 12 | 1 | 2 | 0 | 0 | 0 | 0 | 0 | 0
    > | WILLIAM JONES | 6 | 7 | 3 | 12 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0
    > | JOHN JONES | 6 | 7 | 3 | 12 | 1 | 1 | 2 | 0 | 0 | 0 | 0 | 0
    > | PETER JONES | 6 | 7 | 3 | 12 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0
    > | WILLIAM JONES | 6 | 7 | 3 | 12 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0
    > | ELIZABETH JONES | 6 | 7 | 3 | 12 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0
    > | JOHAN JONES | 6 | 7 | 3 | 12 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0
    > | WILHELM JONES | 6 | 7 | 3 | 12 | 2 | 1 | 2 | 0 | 0 | 0 | 0 | 0
    > |
    >
    > The zero's are place holders, they get replaced with > 0 digits as the
    > newer generations come along.




  5. #5
    Dave Unger
    Guest

    Re: Sorting

    Hi David,

    I'm probably not making myself clear.

    I am doing all my sorting with VBA macros, and it all works fine, I can
    sort the whole list by name, or by index. When I say sorting by index,
    I mean sort the index columns, right to left, that's a "normal" sort.
    What I'm trying to achieve as well, is to come up with a sort that
    group's all the siblings together.

    The only way I've been able to do that is shift all the index to the
    right, and then sort.

    e.g. take 6 7 3 2 1 0 0 0 and make it 0 0 0 6 7 3 2 1, then sort right
    to left, as before.

    I'll give your routine a try and let you know the results. Thanks a
    lot for now, appreciate your time.

    regards,

    DaveU


  6. #6
    Dave Unger
    Guest

    Re: Sorting

    Hi David,

    I tried your routine, and it worked perfectly! That's exactly what I
    was trying to accomplish. I haven't had a chance to analyze why it
    works, but I'll save that for another day.

    Thanks for all your help, much appreciated.

    regards,

    DaveU


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1