Forum Discussion

PieGuy's avatar
PieGuy
Copper Contributor
Dec 16, 2022

Excel Macro or Pivot Table Help

I have a csv file, that i need to repeat certain cells in certain columns, and then also delete empty rows AND certain cells that would move that data up, so every row contains data with no blanks.  ...
excel
Macros and VBA
PieGuy's avatar
PieGuy
Copper Contributor
Dec 22, 2022
ahhh. so sorry about that. I have a windows computer too so will try on that. thanks!
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 26, 2022

PieGuy 

If by any chance the Power Query option solves the issue, there's a mark as solution at the bottom of the post that helps thos who search this site...

 

Doable with Excel 365 on MacOs or Windows:

in B1:

=LET(
  FILLDOWN, LAMBDA(array,
    SCAN(,array, LAMBDA(seed,x, IF(x<>"", x, seed)))
  ),
  InRows, MAX((in!A:A<>"")*ROW(in!A:A)),
  InCols, SEQUENCE(,17),
  In,     INDEX(in!A:Q, SEQUENCE(InRows), InCols),
  Array,  CHOOSECOLS(IF(ISBLANK(In),"",In), {1,3,6,12,14,17}),
  Values, DROP(Array,1),
  FilledDown, HSTACK(
      REDUCE(CHOOSECOLS(Values,1),SEQUENCE(,3,2),
        LAMBDA(seed,col, HSTACK(seed, FILLDOWN(CHOOSECOLS(Values,col))))
    ),
    CHOOSECOLS(Values,{5,6})
  ),
  FilteredQty, FILTER(FilledDown, CHOOSECOLS(FilledDown,5)<>""),
  VSTACK(TAKE(Array,1), FilteredQty)
)

 

Resources