Custom Sort: By Last name but keep different last names together

Please and thank you to the group...

I have a list of members. Column A is Member Number. Column B is Member Letter. Column C is Last Name, Column D is First Name.

It looks like this;

12345 0 Smith John
12345 A Smith Jane
12345 B Smith Bobby

So far so good, everything is sorted properly. HOWEVER, if a spouse has a last name different than the rest of the family, that person gets sorted separately from the rest. I would like to sort the entire list by Last Name then Member Number, then Member Letter AND keep the families together regardless of last name so it would look like the below example.

12345 0 Smith John
12345 A Smith Jane
12345 B Smith Bobby
54321 0 Young Alan
54321 A Jones Betty
54321 B Young Joe

If you sort your second example first by Member Number and then by Last Name you get the exact order that you have specified - at least for this one example. This relies on family members having the same Member Number as is the case in your example. If this does not do what you want then please provide a further more general example.

I just don't see a way to get Excel's sort capability to handle this.

How about the following alternate approach?

Add a new "Sort order" column and have it populated it with numbers reflecting your desired sort order. This can be done more or less automatically as follows:

I assume you have headers is row 1 and col-A is your newly created sort order column and ID, Letter, Last name and First name are in cols B, C, D and E respectively.

1. Sort your member list by last name - important!
2. Put the following formula in A2. Note that this is an array formula and must be committed with CTRL-SHIFT-ENTER
   
   Formula:

   Please Login or Register  to view this content.

3. Copy the A2 formula down as far as necessary
4. This generates a sort key in col-A ordered as you require.
5. Select col-A, copy, paste-values.
6. Now sort by col-A and your member list should be ordered as you require.

The attached workbook uses your latest test data and reflects the above process just prior to the copy / paste-values step.

Let me know if this works for you.

Glad it worked out for you. Thanks for the feedback and rep

>>> EDIT: porucha vevrku's solution does the same thing as mine but in a much much more straightforward manner. The only limitation is that it's currently limited to 4 family members, but his formula is easily extensible to fix that if necessary.