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

Lorenzo 1. No triangle. When i open up Data > Connections (I use a macbook) it brings up the following screen. Thanks!

Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 22, 2022

PieGuy

 

I use a macbook 

This explains things. The query was designed on Excel for Windows. Unfortunately Power Query on Excel for Mac isn't at the same stage yet and I have no idea which version - if any - of Excel for Mac would allow refreshing that query

 

Nothing else I can do for you I'm afraid. A suggestion though: for next times please read Welcome to your Excel discussion space!

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

     

Share

Resources