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
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 21, 2022

PieGuy 

...went back to Output Query and right clicked and refreshed and nothing happened. any suggestions?

Nope 😞

 

Within Excel, go to Data (tab) > Queries & Connections (a pane opens on the right side of the screen)

#1 Do you see a triangle next to QueryOutput?

#2 If you see a triangle:

- Double-click on QueryOutput (the Power Query Editor opens)

- Click on the last APPLIED STEPS (on the right side of the screen)

What is the exact error message?

PieGuy's avatar
PieGuy
Copper Contributor
Dec 21, 2022

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

     

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

Resources