Dec 16 2022 02:29 PM
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!
Dec 16 2022 08:57 PM
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
Dec 19 2022 09:21 AM
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.
Dec 19 2022 01:29 PM
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
Dec 19 2022 01:51 PM
Dec 19 2022 09:27 PM
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
Dec 21 2022 01:11 PM
@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.
Dec 21 2022 01:35 PM
...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?
Dec 21 2022 02:41 PM
@Lorenzo 1. No triangle. When i open up Data > Connections (I use a macbook) it brings up the following screen. Thanks!
Dec 21 2022 06:37 PM
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!
Dec 22 2022 10:45 AM
Dec 26 2022 02:30 AM
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)
)