Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Excel Macro or Pivot Table Help

Copper Contributor

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.   And I need to do this for each group of rows that has a new number (a PO#) in column A. 
I've attached screenshots of before and after. 

In Before - i need to repeat cells column C, F, L until there is a new cell in row 22. Then repeat it again for the info below cell 22 which may or may not be different. it's a new customer once the number in Column A changes.
I then need to delete the blank rows, and also annoyingly, delete the cells from M onwards to remove the blanks and shift cells up in line with the info in column L.
Any help much appreciated.  My excel skills are very intermediate but willing to learn!BeforeBeforeAfterAfter

11 Replies

@PieGuy 

This sounds doable with Get & Transform aka Power Query. Could you:
- Upload & share (ie with OneDrive or any other file sharing service) a representative sample CSV with say 4 or 5 PO#
- List precisely the columns you want to keep

@L z. TPC CSV OneDrive.csv

Link is linked. Thanks for the reply!! I would like to retain:
Columns A, C, F, L, N, Q

 

That's it! 

Thanks in advance for any assistance.

 

@PieGuy 

 

The file you shared is not a CSV file but an Excel file with a .csv extension

Please clarify what your actual source file type is and re-share the actual file - Thanks

Hi there - that was the actual file. sorry, i refer to excel files with csv extensions as csv files! the output file is, in fact, an excel file with a csv extension.

Hi @PieGuy 

i refer to excel files with csv extensions as csv files! the output file is, in fact, an excel file with a csv extension Very confusing. Not my call though...

 

I assumed you would have to do the same thing on a regular basis...

- Download the attached file

- Go to sheet Param

- In B1 enter the full path to your non-csv file

- Switch to sheet QueryOutput

- Right-click in the blue table > Refresh

@L z. 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. 

@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?

@L z. 1. No triangle. When i open up Data > Connections (I use a macbook) it brings up the following screen. Thanks!Screen Shot 2022-12-21 at 5.40.06 PM.png

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

ahhh. so sorry about that. I have a windows computer too so will try on that. thanks!

@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:

Sans titre.png

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