I was asked if it was possible to automatically reduce the ‘Opening Hours’ of a business to the minimum number of lines by finding the patterns in the table and exploiting them.
The top table lists the opening hours for each day.
The Summarize! button finds the unique patterns in the opening hours and adds each to the summary table along with a list of the days where it appeared.
It first extracts the Unique() instances of the opening hours text. Then adds that to the summary table. To list all the days for that opening hours pattern, it uses a Filter() to find all the days in the top table that have that particular pattern.
The formulas in the Summary table then reduce this to the final text on the right.
First, the Seq? function must determine if the list-of-days is a complete sequence or not.
If it is a sequence, then we use the first and last day of the list.
Otherwise we must list all the days in the list.
The summary is sorted by the first day of the list.
The Seq? formula scans through the list of days using an index
ie; Sequence(2, Days.Count()) .FormulaMap(
We name the index for later use with the CurrentValue.WithName( N,
Then we return the difference between this day number, Days.Nth(N) nd the previous Day.Nth(N-1)
this returns as a list of the differences
We are looking to see if ALL the differences are 1 (ie the days are in sequence with no gaps)
So we use Unique() to collapse the list, and if its a single number equal to one, then its a sequence!
If Seq? is true - its a sequence; we list the first and last days of the list.
Otherwise its not a sequence, so we list ALL the days in the list.
Hope people find this useful.
Respect Max