How to insert row in array based on value

Question

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.

How about just using conditional formatting?

TheMaster
– TheMaster

2022-09-30 15:59:43 +00:00
Commented Sep 30, 2022 at 15:59 — TheMaster
– TheMaster, Commented Sep 30, 2022 at 15:59

doubleunary · Accepted Answer · 2022-10-03 13:08:15Z

1

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.

edited Oct 3, 2022 at 13:08

answered Sep 30, 2022 at 16:59

doubleunary

22k13 gold badges38 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TheMaster Over a year ago

While this code snippet may be the solution, including a brief explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

doubleunary Over a year ago

Edited the answer to include a link to an explanation. It is a well-known pattern that has been in use for several years.

Klon Aquwerttag Over a year ago

@doubleunary Here is the link of sample, which is similar to what i use rn. There is also a sheet named 'Goal' where you can see what i'd like to acomplish (dont mind colors there) docs.google.com/spreadsheets/d/…

doubleunary Over a year ago

Edited the answer to match your sample spreadsheet.

Klon Aquwerttag Over a year ago

@doubleunary Thanks for help, it works perfectly. Is there a way to insert date in those blank rows :) As of StackExchange, yea i am new so i cant Vote Up your answer so i marked it :)

|

Collectives™ on Stack Overflow

How to insert row in array based on value

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related