0

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?

1

1 Answer 1

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.

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.