Transaction report export to Excel issues with Splits

Peter Isakson

I often export longs lists of transactions to Excel so I can use its features such as pivot tables to analyze or summarize. For example, export a Transaction style report of all transactions from a checking account, then use Excel to group by Category. To do this correctly, accounting for all categories, you need to activate "include splits" in the report settings. Unfortunately the exported file puts blanks in the cells for Date, Check Number and Description (Payee) for all but the 1st split line (that is, split numbers 2, 3, etc. have blank cells). Analyses such as database functions, pivot tables, filters, etc. don't work when some key columns have blanks in any row. I have to go in and tedioulsy (manually) copy down the "parent" row values. It would be very good if the exported format file did not use these blanks, rather, copied the parent values into every row. If there is a need to actually have these blanks for some people, perhaps have the choice as a report option when you have selected the "show splits".

mshiggins

From C. D. Bales:

"I have to go in and tedioulsy (manually) copy down the "parent" row values".

It seems to me someone once posted a way to have Excel do that for you, but I do not recall the details now. Perhaps they will post again.

"It would be very good if the exported format file did not use these blanks ...".

Except for those who wanted the data just as it was in Quicken.

You should be able to significantly reduce the number of blank fields from split lines by your choice of sequence in the Quicken report.

If you sort Quicken report transactions by Category, for example, Quicken should automatically open all the splits and sort all the transactions/split-lines on Category. 

And unless there are two or more split lines for the same transaction with the same Category, all the fields (columns) for split lines should be populated; since a split line not located with its parent (or other splits from same parent) would otherwise be lacking a knowable date, payee, num or account.

[When you initiate a discussion; you should include your Quicken year, edition, and release; and your operating system. Example: Q2017 Deluxe R16.2; Windows 7. And the country, if not U.S.]

mshiggins

The Excel trick for quickly copying the top cell to blank cells below is explained here:

https://thesoftwarepro.com/excel-tips-how-to-fill-blank-cells/

Peter Isakson

Thanks for answering.

mshiggins, for this comment "Except for those who wanted the data just as it was in Quicken", I did note that maybe it could be an option when creating the export. That is ideally what I (at least) would like Quicken to develop, not to remove a function that exists that some may prefer.
That Excel trick is helpful, but doesn't really apply to my case. That is useful when you have a few "header" type values with a lot of blanks below each "header" row. In my case it is rather a lot of "headers' with perhaps 1, 2 or 3 rows each with balnks. It is still very tedious to do each one manually. I suppose I could try writing amacro to do this, but I don't want to bother.
I will try the idea to sort the report by category. I don't use the same category in two different splits in one tx, except maybe a few by mistake, which can be corrected if I can figure out a way to find them all.

Peter Isakson

I can't edit my posts I guess?
I use Q Deluxe R18.15, Windows 10, US

mshiggins

Peter Isakson said:

That Excel trick is helpful, but doesn't really apply to my case. That is useful when you have a few "header" type values with a lot of blanks below each "header" row. In my case it is rather a lot of "headers' with perhaps 1, 2 or 3 rows each with balnks. It is still very tedious to do each one manually. I suppose I could try writing amacro to do this, but I don't want to bother.

The Excel trick is exactly what will fill on all the blank cells for the splits. Did you try it?

Peter Isakson

Yes, I did. As far as I can see, that only works when there are a lot of blanks that ALL have the same "header" value copied down. My use case is not like that. I can of course do that one by one for each transaction, but that is exactly the extra work I am trying to avoid.