Re-order a table based on values in a column of another table

[Mark Gravo]

So I'm struggling to adequately describe in words in the title what I need to achieve.

I have a results spreadsheet for our sailing races, and one format of our racing is the Mark Foy Handicap Start. My main spreadsheet has a worksheet for each race, a list of the boat names alphabetically, and calculations for elapsed time, the handicap for each boat and calculations for handicap times, places and adjustments.

Typical Sailing races - each boat has a performance "handicap" in minutes. When all boats start at the same time, the handicap results are the elapsed race time less their handicap. The "fastest" boat has zero handicap. For handicap starts, the boats start a number of minutes before the race start time. This is in the table, based on the schedule start time of 13:00 (1 p.m.). Note the table is in boat name alphabetical order.

Handicap times are adjusted based on the handicap results each race, so the "Current Handicap" (col "B") will change and hence the starting order and start times for each boat will change each time such a race of this format is held.

The start procedure has a warning signal 5 minutes before the first boat, then a preparatory signal 4 minutes before the first boat. For each boat, a board showing their handicap time is raised 1 minute before their start and lowered at their start time.

To make it easier for the race committee, I want to have a set of tables with "Alternate Sequence Times" that lists the various signals and their times, and the boat names and their start times, based on a set of alternate race start times in 5 minute intervals. The bit I need help with is the variability of the order of the boats and the differences in handicap times.

The table with red border is a manual representation of what I'm looking for, based on the list of skiffs (not likely to change) and the current handicaps shown. The only thing that might change is if a skiff can't race and I would put "DNC" in column D of the table (Did Not Compete). That would need to the "code" to not include that boat in the list or calculations. If there's a solution to do all of this then I would just replicate for a number of alternate start times. My preference is not to use a macro or VBA if at all possible.

The main file is already in OneDrive so it was easier for me to just strip down a copy and share it.
H10s Race Results 2022-23 Questions.xlsx

 

[Peter Bartholomew]

Firstly, if you are using anything other than Excel 365, replace it. Set a 365 licence to Insider beta because the array shaping functions are new.

= LET(
      warning,    HSTACK("Warning signal",     MAX(handicap)+5, MIN(StartTime) - 5*min),
      pararatory, HSTACK("Preparatory signal", MAX(handicap)+4, MIN(StartTime) - 4*min),
      start,      HSTACK(skiff&" start"&¶&IF(handicap>0, "Board "&handicap&" down", "Race start"),
                                               handicap, StartTime),
      board,      HSTACK(IF(handicap>0, "Board "&handicap&" up","Preparatory signal down"),
                                               handicap+1, StartTime - 1*min),
     events,      VSTACK(warning, pararatory, start, board),
     SORT(events,3)
   )

The edited file is presumably on your OneDrive as a new version?

 

[Mark Gravo]

Hi Peter,

Thanks for the above.

I'm on 365, not 100% sure if the others who may access will be on that. I'll probably be driving that spreadsheet mostly so no drama.

Yes, that linked file is just a partial copy of the main file (just one worksheet of many).

Have just opted in to the Insider, thanks!!

 

[Peter Bartholomew]

Sorry if my changes have an adverse effect on your progress. I am not used to managing work in a collaborative environment.
What I have attempted to do is move the raw data into tables so that, for example, a changing number of entries may be accommodated from sheet to sheet. I have also turned the defined names into sheet local names so that copying the sheet will create new names for the next event.
Also, because the formula is so large and cumbersome, I have defined a Lambda function to hold it, giving a much cleaner worksheet formula.

Worksheet formula
= STARTORDER(skiff,startTime,handicap);

STARTORDER
= LAMBDA(skiff, start, hcap,
    LET(
        warning, HSTACK("Warning signal", MAX(hcap) + 5, MIN(start) - 5 * min),
        pararatory, HSTACK(
            "Preparatory signal",
            MAX(hcap) + 4,
            MIN(start) - 4 * min
        ),
        startText, HSTACK(
            skiff & " start" & ¶ &
                IF(hcap > 0, "Board " & hcap & " down", "Race start"),
            hcap,
            start
        ),
        boardText, HSTACK(
            IF(hcap > 0, "Board " & hcap & " up", "Preparatory signal down"),
            hcap + 1,
            start - 1 * min
        ),
        events, VSTACK(warning, pararatory, startText, boardText),
        SORT(events, 3)
    )
);

Even the formulas in the table look different, e.g.

= IF([@[Finish Time]]="","",
     IF(OR([@[Finish Time]]={"DNS","DNC","DNF"}), [@[Finish Time]],
        [@[Elapsed Time]]-[@[Current Handicap]]*min
     )
  )

Clearly, it is up to you what you adopt, but there should be some food for thought!