Vyger
03-28-2011, 05:18 PM
I am fairly new to VBA and working a large group of census records. I can apply sort logic to group by location, surname, position in household and age, I nopw need to work within those groupings to achieve the best family login and grouping possible.
At present I have achieved a partial result as there are many cases exist where the same location has several families of the same surname.
I have built a key field where the Head of Household is "0" and the spouse is "1" children and other relations have other ascending key numbers.
I have also built the routine below to sort within that location group making the best guess of husband and wife based on age.
r = 809: dup = 5: famcount = 24 ' temp variable settings for test
Range(Cells(r, 1), Cells(r + (dup * 2), 19)).Sort _
Key1:=Cells(r, 7), Order1:=xlDescending, _
Key2:=Cells(r, 17), Order2:=xlAscending
For x = 0 To dup * 2 Step 2
Range(Cells(r + x, 1), Cells(r + x + 1, 19)).Sort _
Key1:=Cells(r, 17), Order1:=xlAscending
Next x
My next step would be to reorder some rows through logic which I can not simply achieve a result through sorting.
For example in the example in the attachment I can group husbands and wives based on age through the routine above.
What is beyoud my experience at present is reordering the fields beyond sorting. My routine will group husband and wife based on age as shown, but I need to then group the children as best possible for a sequential file output family by family.
Logic shows in this example that children 824 & 825 must belong with parents 809 & 810 and should appear directly after them. No logic can be exact regarding the remaing children but some can be applied for example couple 817 & 818 are two young to be parents of several of the children.
My question is not how to apply the logic but rather how to reorder the rows when I apply it. I know I can copy rows and paste rows but I would overwrite the existing contents so do I need to be thinking of doing the reorder within an array or is there a more direct approach in Excel that I am unaware of at present.
Sorry for the long winded post and thanks in advance, I'm sure there is an easy answer that I am simply unaware of at present.
At present I have achieved a partial result as there are many cases exist where the same location has several families of the same surname.
I have built a key field where the Head of Household is "0" and the spouse is "1" children and other relations have other ascending key numbers.
I have also built the routine below to sort within that location group making the best guess of husband and wife based on age.
r = 809: dup = 5: famcount = 24 ' temp variable settings for test
Range(Cells(r, 1), Cells(r + (dup * 2), 19)).Sort _
Key1:=Cells(r, 7), Order1:=xlDescending, _
Key2:=Cells(r, 17), Order2:=xlAscending
For x = 0 To dup * 2 Step 2
Range(Cells(r + x, 1), Cells(r + x + 1, 19)).Sort _
Key1:=Cells(r, 17), Order1:=xlAscending
Next x
My next step would be to reorder some rows through logic which I can not simply achieve a result through sorting.
For example in the example in the attachment I can group husbands and wives based on age through the routine above.
What is beyoud my experience at present is reordering the fields beyond sorting. My routine will group husband and wife based on age as shown, but I need to then group the children as best possible for a sequential file output family by family.
Logic shows in this example that children 824 & 825 must belong with parents 809 & 810 and should appear directly after them. No logic can be exact regarding the remaing children but some can be applied for example couple 817 & 818 are two young to be parents of several of the children.
My question is not how to apply the logic but rather how to reorder the rows when I apply it. I know I can copy rows and paste rows but I would overwrite the existing contents so do I need to be thinking of doing the reorder within an array or is there a more direct approach in Excel that I am unaware of at present.
Sorry for the long winded post and thanks in advance, I'm sure there is an easy answer that I am simply unaware of at present.