Forum Discussion

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
    Silver 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)
)
    • DrExcel_Excel_MVP's avatar
      DrExcel_Excel_MVP
      Copper Contributor
      Nov 20, 2023
      many thanks for your contribution

Resources