google-sheetsgoogle-sheets-formula

Add empty row in an array, each time the content of a given column is different

I try to find a one-formula approach to be able to automatically add an empty row each time the content from one given column changes.

Ideally a one-line formula; I have seen some approached using reduce() tocol() vstack() but could not make it work properly.

In below example, adding an empty row each time the column A value is different:

Start data

Fruit Amount
Apple 10
Apple 20
Pear 4
Banana 10
Banana 5
Cherry 20

Desired output

Fruit Amount
Apple 10
Apple 20
Pear 4
Banana 10
Banana 5
Cherry 20

Many thanks in advance

Solution

  • You may try:

    =reduce(A1:B1,A2:index(A:A,match(,0/(A:A<>""))),lambda(a,c,vstack(a,ifna(if(c<>offset(c,1,),vstack(offset(c,,,,2),),offset(c,,,,2))))))

    enter image description here