Forum Discussion

DrExcel_Excel_MVP's avatar
DrExcel_Excel_MVP
Copper Contributor
Nov 19, 2023

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

                   

excel
Excel on Mac
Excel on mobile
Formulas and Functions
office 365
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 20, 2023

    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.

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Nov 19, 2023

    =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)))
)
    • djclements's avatar
      djclements
      Bronze Contributor
      Nov 19, 2023

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

Resources