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

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" wrote in message ups.com...
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

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

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.

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

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