Mar 27 2023 08:35 AM
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!
Mar 28 2023 12:41 AM
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:
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!
Mar 28 2023 02:02 PM
@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!
Mar 29 2023 03:24 AM
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
Mar 29 2023 06:37 AM
SolutionThis 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.
Mar 29 2023 12:39 PM
Mar 29 2023 01:58 PM
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.
Mar 29 2023 02:52 PM