How do I get data to repeat itself in a register report, or any report?
Options
cbendy
Member ✭
When I download a report, is there a way to set the data to repeat? Example, I enter a transaction, and split cost into multiple line items. When I download the report, only the first line has details in many columns; date, account, desc, etc. So when I download the report into an Excel workbook, I can't utilize search functions, can't run pivot tables, or query the data without first spending an hour or more, coping and pasting the missing data. There has to be a way to request repeat all items labels in a report. Please help!
0
Best Answer
-
I'm not aware of a way.
As a workaround, after you download the report into Excel, you could use the Excel IF function to do this for you rather quickly.
Say you have Date, Account, and Description in Excel columns A-C only on the first line for your split transactions. That's 3 columns. Insert 3 columns to right of column C and copy the headings from them into your new columns D-F.
In the first data row in each column D-F, use IF to check for a Date in Column A in the same row. If the date is there, return the data for that column and row. If the date is not there, return the data from the prior row. Say your data starts in Excel row 2:
Column D : If(A2<>"",A2,A1)
Column E : If(A2<>"",B2,B1)
Column F : If(A2<>"",C2,C1)
Then fill/copy the 3 new formulas in D-F Row 2 into Row 3 and all the way down to the bottom of your data range.
Instead of Columns A-C, use Columns D, E, F, ... in your searches, etc.
This should take you less than 10 minutes on your first try, On subsequent report downloads, insert the same 3 columns and copy your formulas from your previous worksheet's D-F into your new one. That should take you less than a minute.
Securities transactions may be more difficult because there may not always be data in Columns B and C, but you should be able to figure it out in Excel.
Hope this helps
-CareyWin 11 - Quicken Premier - v54.16
1
Answers
-
I'm not aware of a way.
As a workaround, after you download the report into Excel, you could use the Excel IF function to do this for you rather quickly.
Say you have Date, Account, and Description in Excel columns A-C only on the first line for your split transactions. That's 3 columns. Insert 3 columns to right of column C and copy the headings from them into your new columns D-F.
In the first data row in each column D-F, use IF to check for a Date in Column A in the same row. If the date is there, return the data for that column and row. If the date is not there, return the data from the prior row. Say your data starts in Excel row 2:
Column D : If(A2<>"",A2,A1)
Column E : If(A2<>"",B2,B1)
Column F : If(A2<>"",C2,C1)
Then fill/copy the 3 new formulas in D-F Row 2 into Row 3 and all the way down to the bottom of your data range.
Instead of Columns A-C, use Columns D, E, F, ... in your searches, etc.
This should take you less than 10 minutes on your first try, On subsequent report downloads, insert the same 3 columns and copy your formulas from your previous worksheet's D-F into your new one. That should take you less than a minute.
Securities transactions may be more difficult because there may not always be data in Columns B and C, but you should be able to figure it out in Excel.
Hope this helps
-CareyWin 11 - Quicken Premier - v54.16
1 -
Instead of using Transaction detail report, have you tried using the Itemized Categories Report?This report, when "Updated to show / Transaction Detail" will give you one line for each split, sorted and subtotaled by category and subcategory.However, export of this report to CSV or XLSX format still is not a true, machine-readable data file dump which a program can read without prior extensive cleanup and modification.0
-
Thank you Carey, I am so grateful for your comment. It was very helpful. I'm frustrated with Quicken that I have to do this manual trick, but I appreciate you knowing and passing along your expertise. This will save me so much time.1
-
Please see this Idea post for more information on how to get the data to repeat for splits.
https://community.quicken.com/discussion/comment/20067294
You can also vote on the idea to encourage Quicken to build in this capability.QWin Premier subscription0 -
Glad to help. One thing I should add... If you decide you want to sort your data, you can first select all of the data in Columns D-F, then copy it and "Paste Values" back over the formulas. The computed values will replace the formulas.cbendy said:Thank you Carey, I am so grateful for your comment. It was very helpful. I'm frustrated with Quicken that I have to do this manual trick, but I appreciate you knowing and passing along your expertise. This will save me so much time.
Then you can sort however you like and they will sort correctly.
-Carey
PS - The other guys here have some really good input as well.Win 11 - Quicken Premier - v54.16
0
This discussion has been closed.