attribute values missing for split transactions in reports

JimInMN
JimInMN Quicken Windows Subscription Member ✭✭
edited May 2022 in Reports (Windows)
In a report, such as a transaction report, Quicken gives the option of showing splits or not. If "Show splits" isn't selected, the report shows the values for Date, Account, Num, Description, and Memo of the overall transaction, while listing "--Split--" for Category and Tag. That's fine.

If "Show splits" is selected *and* the report is sorted by Date/Account or some other attribute that causes the split lines to be adjacent in the report, each split line shows the line-level Memo, Category, Tag, and Amount values but Date, Account, Num, and Description values are *missing* for the second and subsequent split lines.

The missing attribute values are a problem when I export the report for analysis in Excel. If I re-sort the data in Excel by a different attribute, I can't tell what the values for Date, etc. should be for the second and subsequent split lines. If I create a pivot table from the exported data, I can't correctly aggregate or filter the data by Date, etc.

I consider the missing attribute values a bug, not a feature. The simple solution is for Quicken to show all attribute values for all split lines regardless of how the report is sorted.

Is there any way to work around this problem? If the report is sorted such that the split lines aren't adjacent in the report, then Date, Account, Num, and Description are shown for all split lines. However, there is no attribute you can sort on that *guarantees* that the split lines aren't adjacent. For instance, if the split lines all have the same Category and the report is sorted by Category, then Date, etc. are missing for all but the first split line.

Best Answer

Answers

  • Rich_M
    Rich_M Quicken Windows 2017 Member ✭✭✭✭
    edited October 2020
    @JimInMN This is definitely an issue with exporting reports to Excel from Quicken. 

    Unfortunately it's a, "what you see is what you get", export.

    With an export to Excel, you would definitely want all the columns populated, no matter what the sort order is, so this is a sorely needed enhancement.
    Quicken 2017 Premier - Windows 10 Pro
  • JimInMN
    JimInMN Quicken Windows Subscription Member ✭✭
    Thanks for the comment. I'm a newbie to this community. Does the Quicken staff monitor our comments? How can I submit an enhancement suggestion?
  • Jim_Harman
    Jim_Harman Quicken Windows Subscription SuperUser ✭✭✭✭✭
    edited October 2020
    @JimInMN
    You might try this approach to fill in the blanks after exporting (thanks @mshiggins for finding this)
    https://thesoftwarepro.com/excel-tips-how-to-fill-blank-cells/

    You can Google "Excel fill blank cells with cell above"  (wthout the quotes) for more ideas on how to do this in Excel. 
    QWin Premier subscription
  • JimInMN
    JimInMN Quicken Windows Subscription Member ✭✭
    Jim--A belated thanks for your suggestion. It's a bit awkward, but it works, with one clarification: where it says "enter an equal sign and point to the cell above". it should say "enter an equal sign and a reference to the cell above".

    In my case, i would do this separately for each column where I wanted to copy down values, selecting one column at a time before using the trick. The final step, replacing formulas with values, can be done once for the entire table.

    I see from your profile picture that you and I are high school classmates. Great to hear from you!
  • JimInMN try this -

    Lets say that column A has the Dates from Quicken that have blank rows between dates. Insert a blank column B and copy the first date in column A to column B. Right below that date in column B, type in this formula - =IF(A2="",B1,A2) and then copy it down to the end of column B. Then, column B will be the dates in Column A with all the blanks filled in with the appropriate dates. You can do this with any column of data that has blank rows. After you do this, make sure you highlight column B and do a "Copy/Paste Values" to get rid of the formulas and replace with actual data. This all take less than a minute, and I have converted many large Excel spreadsheets from Quicken without issue.

    Another trick I have learned to do is to insert a blank column A and number it from 1 to whatever number is the bottom of your data by using a simple formula A1 + 1 copied to the end of your data. (starting with 1 typed in cell A1). Again remember to highlight column A and do a "Copy/Paste Values" to get rid of the formulas and replace them the actual values. This way you can sort the file with blank rows and then always get back to the original data sort with the blank rows.

    Thanks!
    Damian
This discussion has been closed.