How to insert row in array based on value-CodePudding

Im using this formula to create array:

= SORT( (FILTER( { (Details)\(Emails)\(INT(Timestamp))\(Note)} ; (Attending="Yes"))) ; 7 ; True)

Details, Emails, Timestamp, Note, Attending are all named ranges from different sheet.

Timestamp column is date format.

Is there a way to split array where new date value appears?

Lets say timestamp in arrray is in column 'G' and G2:G7 is 8/16/2022 and G8:G20 is 8/17/2022

I would like to have row or some kind of indicator in between.

CodePudding user response：

Assuming the results of your sort() formula are in the range sort!A2:G, and you want to insert a blank row every time the value in column sort!F2:F changes, put this formula in cell A2 of a blank sheet:

=arrayformula( iferror( 
  vlookup( 
    sort( 
      { 
        filter(row(sort!F2:F); len(sort!F2:F)); 
        filter(row(sort!F2:F); len(sort!F2:F); sort!F2:F <> sort!F3:F)   0,3 
      } 
    ); 
    { row(sort!F2:F) \ sort!A2:G }; 
    column(sort!A2:G) - column(sort!A2)   2; 
    false 
  ) 
) )

Format column F as Format > Number > Date.

The formula uses separators that work in locales that use comma as decimal mark. To make it work in a locale that uses period as decimal mark, replace the comma there with a period, all semicolons with commas except the one following the first filter(), and the backslash \ with a comma.

This is a well-known pattern to insert a blank row between rows where the value in a key column changes (here, column F). For an explanation of how it works, see Insert blank row after every group for same data.