Forum Discussion

bjerniganpca's avatar
bjerniganpca
Copper Contributor
Mar 27, 2023
Solved

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...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Mar 29, 2023

    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.

bjerniganpca's avatar
bjerniganpca
Copper Contributor
Mar 28, 2023

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!

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Mar 29, 2023

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

Resources