Complex Sorting Problem

[ashish514]

Following is the sample of my data which has to be sorted:

http://www.box.net/shared/npktk537id


What i need is that the data should first be sorted by family sr no., then in that family, first head of the family should figure (code 1), then his wife (2) and then the rest members of the family should be sorted by age.

Right now when i am sorting first by sr. no. and then by relation, grandson is coming before son and also daughter is coming before son even if the son is elder.
If i sort first by sr. no and then by age, then in case there is a father or mother of the head of the family or in some cases if wife is elder, i do not get the desired result.

 

[Peter_SSs]

Hint on getting help from the forum:
Many of the experienced helpers here choose not to download files from other sites. Also, due to security issues at work sites, many users are unable to download such files.

You will get many more potential helpers if you explain your problem clearly in words and, if needed, post a small screen shot or two directly in your post. My signature block below suggests 3 ways you can do that. Test them out in the Test Here forum.

 

[ukmikeb]

Hi

Create a helper column which references your relation column (B in example) and enter following formula -

=IF(ISNUMBER($B2),$B2,99)

and copy down

Then sort Family No, Helper Column, Age

hth