Google Sheets - Auto group and sort

Question

I've got a bunch of data, in this instance it's lists of fixtures for my son's juniors football team, which I'd like to order for ease. The age group is split into sub teams, e.g. U10 Reds, U10 Blues etc. I can order all the fixtures by the sub-team, which is progress. What I'd really like to do though is order them by date/time of the fixture, but within the sub-teams. So I've currently got something like this:

Time	Team
9:00	Blues
9:15	Blues
9:30	Blues
9:45	Blues
10:00	Reds
10:15	Reds
10:30	Reds
10:45	Reds
9:00	Whites
9:15	Whites
9:30	Whites
9:45	Whites

But what I'd like is this:

Sorted	Team
9:00	Blues
9:15	Blues
9:30	Blues
9:45	Blues
9:00	Whites
9:15	Whites
9:30	Whites
9:45	Whites
10:00	Reds
10:15	Reds
10:30	Reds
10:45	Reds

Any idea how I can do this?

Answer 1

Use a formula to copy and sort the data into new columns, or into another tab, like this:

=lambda( 
  times, teams, ascending, 
  lambda( 
    sortBy, 
    sort( { times, teams }, sortBy, ascending, times, ascending ) 
  )( 
    map( 
      teams, 
      lambda( 
        team, 
        lambda( 
          sortKey,
          if( 
            len(team), 
            sortKey & team, 
            iferror(1/0) 
          ) 
        )( 
          text(min(filter(times, teams = team)), "HH:mm") 
        ) 
      )
    ) 
  ) 
)(  
  A2:A, B2:B, true 
)

Modify the range references A2:A and B2:B so that they point to the actual locations of times and teams. Formula results will look like this:

Time	Team		Solution	Team
9:00	Blues		9:00	Blues
9:15	Blues		9:15	Blues
9:30	Blues		9:30	Blues
9:45	Blues		9:45	Blues
10:00	Reds		9:00	Whites
10:15	Reds		9:15	Whites
10:30	Reds		9:30	Whites
10:45	Reds		9:45	Whites
9:00	Whites		10:00	Reds
9:15	Whites		10:15	Reds
9:30	Whites		10:30	Reds
9:45	Whites		10:45	Reds

If you need to sort the data in place, Insert > Column for sort keys, and then Data > Sort sheet by the new sort key column. You can insert suitable sort keys in the new column like this:

={ 
  "Sort keys"; 
  lambda( 
    times, teams, 
    lambda( 
      sortBy, 
      sortBy 
    )( 
      map( 
        teams, times, 
        lambda( 
          team, time, 
          lambda( 
            sortKey,
            if( 
              len(team), 
              sortKey & team & text(time, "HH:mm"), 
              iferror(1/0) 
            ) 
          )( 
            text(min(filter(times, teams = team)), "HH:mm") 
          ) 
        )
      ) 
    ) 
  )(  
    A2:A, B2:B 
  ) 
}

The result will look like this:

Time	Team	Sort keys
9:00	Blues	09:00Blues09:00
9:15	Blues	09:00Blues09:15
9:30	Blues	09:00Blues09:30
9:45	Blues	09:00Blues09:45
10:00	Reds	10:00Reds10:00
10:15	Reds	10:00Reds10:15
10:30	Reds	10:00Reds10:30
10:45	Reds	10:00Reds10:45
9:00	Whites	09:00Whites09:00
9:15	Whites	09:00Whites09:15
9:30	Whites	09:00Whites09:30
9:45	Whites	09:00Whites09:45

Note that the sort key formula needs to go the column label row, i.e., in row 1 in most spreadsheets.