clean up downloaded .csv file for import into program (final one)

hello all,

I have a .csv file that I'm opening in excel in order to clean up a few things before I can successfully import it into another program. I've attached a spreadsheet to help explain what I'm trying to do. the first sheet named "before" is what I start with. the second sheet named "after" is what I'm looking for. I'm using the yellow highlighted rows to explain what I'm looking for.

(a) there are certain things I can do by simply modifying code that a few of you have provided over the past week. examples follow :
(i) I don't need help replacing the words in the "transaction type" column with actual 3 letter transaction symbols.
(ii) I don't need help deleting columns D (amount) and E (source).
(iii) I don't need help deleting the column header row.

(b) what I do need help with is getting what I'll call "sub transactions" converted to "primary" transactions. an example. look at rows 10 through row 17 on the before sheet. the financial site that I downloaded this from chose to make my life difficult by using a "total" transaction on row 10, then providing all the details on rows 11 through 17. the entire spreadsheet is the same. so, I need the date and transaction type for the "primary transaction" to be copied to it's associated sub transactions. the result is what you see on the "after" tab in rows
(c) next, the "primary" rows are then deleted. in the example provided, this means row 10 on the "before" sheet is deleted.
(d) the final thing I need is a symbol column. so based on the name of each fund, column b (which is an inserted column) is populated with the fund symbols. these symbols are included on the tab named "list".
(e) when done, the "before" sheet looks like the "after" sheet and is ready for me to import into my financial program.

hope this makes sense.

I realize I've had a few requests for help in the past week; and I certainly don't mean to abuse the privilege. this will be the last post you'll see from me in awhile, I'd imagine.

thank you for all the help you guys have provided over the past week. it's been a real help to me.

best regards,

david

excelforum5.xlsx

Tms,

thank you for taking the time to post. I only know how to modify the code I provided in my original post. I tried running the code to see what it does; but it doesn't seem to take into account the fact that some transaction have multiple subtransactions, and some transactions have only one.

would you or anyone else like to take another stab?

thank you.

can someone please take a stab at this?? I'd really appreciate it.

TMS,

thank you very much for taking another stab at it. I very much appreciate it. the code seems to get rid of the duplicates; but it is deleting my "date" column and my "transaction type" column and inserting zero's into these columns. it also doesn't look like it handles the symbols functionality mentioned in (d) of my original post.

if you get a chance, can you take a look?

best regards,

David

TMS,

ok. my mistake. I've been trying to code on my own without completely abusing the kindness of folks on this forum. as such, I had started making some intermediary steps and used your macro on a file that was slightly modified from that which I originally uploaded to this forum. thank you for calling me out on this. I ran it on the file I uploaded and it worked.

would you like to take a stab at (d) in original post to completely close out?

if not, would anyone else like to take a stab? run TMS macro on my file that I uploaded, then see (d) on original post above.

thank you

david

TMS,

a very hearty, albeit somewhat belated (life happened again :>), thank you. this does it.

much appreciated. blue star for you sir!!

and rep too ;>)