Sorting last names alphabetically but keeping together by family ID if last name differs

[bjerniganpca]

I have a list of families and each family has a unique identifier called Family ID.  I want to sort the list alphabetically but keep each family together.  Some families do not share the same last name so an alpha sort by last name ends up with families separated.  Sorting by Family ID keeps the families together but they are not in alphabetical order.  They are in order from "smallest" family ID # to largest, which is arbitrary.  Any ideas?  Thanks!

[#2]

@bjerniganpca 

You can sort the list alphabetically by last name but keep each family together by sorting first by Family ID and then by last name.

This way, families with the same Family ID will be sorted together and within each Family ID group, they will be sorted alphabetically by last name.

Here’s how you can do it in Excel:

1. Select the entire table including headers.
2. Go to “Data” tab and click on “Sort”.
3. In the “Sort” dialog box, select “Family ID” as the first sort column and “Last Name” as the second sort column.
4. Click on “OK”.

You can use a sort list in VBA too.

An example code that sorts a list alphabetically by family ID you can find in the file I have inserted.

This code sorts a list that has family ID in column A.

 You can modify this code to suit your needs. 

The code is from the internet, unfortunately I can't remember the source.

I hope this helps!

[#3]

@NikolinoDE Thank you for the reply!  When I do what you suggested with sorting I end up with the families who have different last names far apart because the primary sort is by last name.  So student Dave Smith and student Lucy Jones are in different places, even though they should be together because they have the same family ID.   If they have the same last name the families do stay together with this method, but that does not accomplish what I am wanting to do.  Let me know if I'm misunderstanding your suggestion.  Thanks!

[#4]

@bjerniganpca 

So it should actually be as desired :).

This code in the file, first copies the data from columns A to C to columns F to H.

Then it sorts the data in columns F to H by the family name (column G) in ascending order and then by the family ID (column F) in ascending order.

This will keep the last names together by family ID if the family name is different.

Hope it helps!

Thank you for your patience and understanding

[#5 · Accepted solution]

@bjerniganpca 

This is a reasonably simple Excel 365 formula

= LET(
    leadName, XLOOKUP(ID, ID, Name),
    SORTBY(Tabelle, leadName)
  )

All it does is identify the first occurring name associated with each ID and sorts on that.

[#6]

Thank you everyone for the help! Peter, I could not get your formula to work (what is Tabelle?). However you pointed me to the answer. I just made a new column called "Last Name for Sort" and did an xlookup to Family ID to pull the first last name found on each Family ID. Then I sorted by that field. Thank you all!

[#7]

@bjerniganpca 

I had picked up @NikolinoDE 's workbook with its German language flavour.  I used the defined name 'Tabelle' to apply to the entire table.  Niko previously had that as a sheet name but I wanted it more specific to the data.  

BTW. My formula does not sort the source data in place, instead it creates a dynamic array sorted as you specified.  Anyway it is good that you have what you require.

[#8]

if possible,provide some data and expected result maybe helpful.