Insert a Blank Row After Every Change of Product

[DrExcel_Excel_MVP]

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))

[#2]

=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) ),
  VSTACK(Table[#Headers], IF(MOD(Order,1),"",CHOOSEROWS(Table,Order)))
)

[#3]

@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)
)

[#4]

many thanks for your contribution

[#5]

@DrExcel_Excel_MVP 

=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.