Complex Sorting Problem

ashish514

New Member
Joined
Feb 10, 2011
Messages
47
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0
Hi

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

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

and copy down

Then sort Family No, Helper Column, Age

hth
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top