Forum Discussion
PieGuy
Dec 16, 2022Copper Contributor
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. ...
PieGuy
Dec 21, 2022Copper Contributor
Lorenzo WOW! Thanks so much. It looks amazing. i was having a little trouble replicating it though.
- I exporting a new source file,
- saved file to my hard drive
- copied that file pathname to Param / B1
- went back to Output Query and right clicked and refreshed and nothing happened. any suggestions? Thanks again for all your help. the final product is perfect.
Lorenzo
Dec 21, 2022Silver Contributor
...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?
- LorenzoDec 26, 2022Silver Contributor
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) )
- PieGuyDec 22, 2022Copper Contributorahhh. so sorry about that. I have a windows computer too so will try on that. thanks!
- LorenzoDec 22, 2022Silver Contributor
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!