# Insert a Blank Row After Every Change of Product

Copper Contributor

# Insert a Blank Row After Every Change of Product

Insert a Blank Row After Every Change of Product

let's do it step by step:

Step by Step

#1

=SEQUENCE(ROWS(B4:E13))

#2

=B4:B13=B5:B13

#3

=DROP(H4#,-1)

Which is  :  =DROP(B4:B13=B5:B13,-1)

#4

=I4#*1

Which is  : =DROP(B4:B13=B5:B13,-1)*1

#5

=VSTACK(1,J4#)

Which is : =VSTACK(1,DROP(B4:B13=B5:B13,-1)*1)

#6

=FILTER(G4#,K4#=0)

Which is  : =FILTER(SEQUENCE(ROWS(B4:E13)),VSTACK(1,DROP(B4:B13=B5:B13,-1)*1)=0)

#7

=HSTACK(G4#,B4:E13)

Which is : =HSTACK(SEQUENCE(ROWS(B4:E13)),B4:E13)

#8

=IFNA(VSTACK(L4#,M4#),"")

Which is : =IFNA(VSTACK(FILTER(SEQUENCE(ROWS(B4:E13)),VSTACK(1,DROP(B4:B13=B5:B13,-1)*1)=0),HSTACK(SEQUENCE(ROWS(B4:E13)),B4:E13)),"")

#9

=SORT(C18#,1,1)

Which is :  =SORT(IFNA(VSTACK(FILTER(SEQUENCE(ROWS(B4:E13)),VSTACK(1,DROP(B4:B13=B5:B13,-1)*1)=0),HSTACK(SEQUENCE(ROWS(B4:E13)),B4:E13)),""),1,1)

#10

=DROP(I18#,,1)

Which is :

=DROP(SORT(IFNA(VSTACK(FILTER(SEQUENCE(ROWS(B4:E13)),VSTACK(1,DROP(B4:B13=B5:B13,-1)*1)=0),HSTACK(SEQUENCE(ROWS(B4:E13)),B4:E13)),""),1,1),,1)

In one formula :

=LET(

a, B4:E13,

b, TAKE(a, , 1),

c, SEQUENCE(ROWS(a)),

DROP(

SORT(

IFNA(

VSTACK(

FILTER(

c,

VSTACK(1, DROP(b = DROP(b, 1), -1) * 1) = 0

), HSTACK(c, a) ), ), 1, 1), , 1))

4 Replies

# Re: Insert a Blank Row After Every Change of Product

``````=LET(
TableRws, SEQUENCE(ROWS(Table)),
NewProd,  IF(Table[Product] <> INDEX(Table[#All],TableRws,1), SEQUENCE(ROWS(Table),,0)),
Order,    SORT( VSTACK(TableRws, FILTER(NewProd,NewProd)+0.1) ),
)``````

# Re: Insert a Blank Row After Every Change of Product

@Lorenzo and @DrExcel_Excel_MVP Good ones! Just for fun, here's a couple more:

``````=LET(
rng, A2:D11,
v, TAKE(rng,, 1),
r, ROWS(rng),
s, SEQUENCE(r),
n, FILTER(s, IFNA(v <> DROP(v, 1), 0)),
SORTBY(EXPAND(rng, r + ROWS(n),, ""), VSTACK(s, n))
)``````

- OR -

``````=LET(
rng, A2:D11,
v, TAKE(rng,, 1),
s, SEQUENCE(ROWS(rng)),
DROP(SORT(IFNA(HSTACK(VSTACK(s, FILTER(s, IFNA(v <> DROP(v, 1), 0))), rng), "")),, 1)
)``````

# Re: Insert a Blank Row After Every Change of Product

``````=LET(cnt,
SCAN(0,A2:A11,LAMBDA(ini,arr,IF(OFFSET(arr,-1,0)=OFFSET(arr,0,0),ini,ini+1))),
combin,
UNIQUE(HSTACK(A2:A11,cnt)),
IFNA(DROP(REDUCE("",SEQUENCE(ROWS(combin)),LAMBDA(x,y,VSTACK(x,FILTER(A2:D11,BYROW(HSTACK(A2:A11,cnt)=INDEX(combin,y,),LAMBDA(x,AND(x)))),{""}))),1)
,"")
)``````

Another alternative could be this formula.