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"), Active***.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.


.