Google Sheets - Auto group and sort

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?

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.

• That's amazing, thank you! The only question I have is where in the formula do I specify other columns to show based on that function. For example venue, opponent Commented Feb 4, 2023 at 22:28
• That is a different question that will require a different solution. Please ask only one question per post. Post a new question. Commented Feb 4, 2023 at 23:11
• I gather that this is your first question at Stack Exchange. See What should I do when someone answers my question? Commented Feb 4, 2023 at 23:11