Forum Discussion
bjerniganpca
Mar 27, 2023Copper Contributor
Sorting last names alphabetically but keeping together by family ID if last name differs
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 na...
- Mar 29, 2023
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.
NikolinoDE
Mar 28, 2023Gold Contributor
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:
- Select the entire table including headers.
- Go to “Data” tab and click on “Sort”.
- In the “Sort” dialog box, select “Family ID” as the first sort column and “Last Name” as the second sort column.
- 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!
bjerniganpca
Mar 28, 2023Copper Contributor
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!
- NikolinoDEMar 29, 2023Gold Contributor
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