Excel Macro or Pivot Table Help

[PieGuy]

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

[#2]

@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

[#3]

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

[#4]

@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

[#5]

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.

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10]

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

[#11]

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

[#12]

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