Non Standard sort beyond my experience at present

[Orada]

Hi all

I am relatively new to VBA so this problem is beyond me at present. It's a conditional sort but with logic so cannot be achieved in the normal multi column conditions. The first example is what I begin with and you can see the logic that this is two families where column A is the persdons age, B is a key field and C is just for information.

     A         B       c
1.   67        0      husband
2.   33        0      husband
3.   71        1      wife
4.   31        1      wife
5.   29        2      son
6.    9        2      son
7.   37        3      daughter
8.    4        3      daughter

The logic concerns the childrens age and logically what parents they can belong to so what I want to achieve in this example is below but it's just beyond me at present and looks like I will need to swap rows or something.

     A         B       c
1.   67        0      husband
2.   71        1      wife
3.   37        3      daughter
4.   29        2      son
5.   33        0      husband
6.   31        1      wife
7.    9        2      son
8.    4        3      daughter

Thanks in advance for any insight or help, I'm sure an example of how to do this will help me build further and achieve what I want.

 

[kpark91]

Hi, Orada.
In each family, will there always be (husband, wife, son, and daughter)?

If so, when the parents have more age than the other parents, they should have older children, both sons and daughters.. Is that correct?

How does a 'key field' have to do with any sorts here? Is it just to more easily identify their roles in the family?

 

[kpark91]

Moreover, for parents there should be only oldest pairs right?

So if there was

M69, F71, M73, F60, M39, F35...

the pairs would be
M73, F71
M69, F60
M39, F35..

Is this correct?

 

[Orada]

There may not always be sons or daughters, I would just like to apply the best logic possible based on the ages.

There are many more columns from census records and the first thing I do is to count a gec code group by surname residence fields then sort by key ascending and age descending.

You are correct that the key field makes sorting easier and 0 is actually for Head of Household.

There are many cases where the head of household may be "unmarried", widowed etc and I have been able to achieve breaking out these individuals into their own households.

What I am stuck with is what I tried to illustrate where I could have 3 or 4 families of the same name in the same location ranging from husband and wife of maybe 70 years to relatively young husband and wife of around 20. There might be an unmarried daughter of 39 years old and a newly born child of 1 year.

There is logic although not exact and I know I will always have to some manual adjustments, I just want to minimise these.

 

[Orada]

I resolved this myself, applying the logic was not a problem and grouping families by name and location had already been achieved, all I was needing to know was how to manually rearrange the rows based on my decisions.

The simple code below works, as I said before I am relatively new to VBA and had not used such before.

Thanks for all replies.

Sub copy_insert()
' use loops and If Then logic to decide the original row ' position and the row where it should appear in the
' worksheet use variables for source and destination  
' in this example src and dest.
src = 5
dest = 2
    ' copy original "source" row
    Rows(src).Select
    Selection.Copy
    
    ' insert at new position in worksheet "destination"
    Rows(dest).Select
    Selection.Insert Shift:=xlDown
    
    ' delete original "source" row which is now a duplicate
    Rows(src + 1).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
End Sub