Community Discussion: How do I get data to repeat itself in a register report, or any report?
Found at:
formatting link
The original poster says: "When I download the report, only the first line has details in many columns; date, account, desc, [sic] etc."
The op appears to be referring to the fact that Quicken reports that show individual transactions, will only display Date, Payee, and Account for the first line of split transactions, when split lines from the same transaction follow immediately after one another (since those fields are the same for every line of a given split transaction).
When such a report is exported to something like Excel, it is difficult to sort on Date or Payee for example, since those values will not be in every row.
The approach provided by Member Imdcareys is probably the only way the original poster is going to consistently get the desired results.
But I think it's possible to create something like an Excel "macro" to do more-or-less the same thing - the advantage being not having to take each of the individual steps required in Imdcareys' approach. [I'm far from being an Excel expert, just going by a fading memory that I have seen something like the "macro" approach somewhere in the past.]
SuperUser UKR offers a perfectly legitimate, albeit potentially less complete/satisfying, alternative.
"Instead of using Transaction detail report, have you tried using the Itemized Categories Report?"
Personally I don't find the Itemized Category report any better than the Banking > Transaction report sorted by Category with the option to "Show splits" checked. Nevertheless, UKR's approach should significantly reduce the number of report rows that are missing the Date, Account and Payee values.
It should also be noted that neither report will guarantee that every split line will contain Date, Account and Payee. Anytime a split transaction contains more than one line with the same category, only one of those lines will display in a Quicken report sorted (or subtotaled) by Category. [One could fiddle around trying to sort on a different field than Category - Tag, perhaps ... though that might just shift the problem to different transactions.]
[I understand the desire to be able to tell Quicken to display every field selected for the report, in every row of the report; but I'm skeptical that Quicken will see this as an economically beneficial project (economically beneficial to both Quicken and Quicken users). Especially if an Excel macro can accomplish the desired result with possibly as little effort as a couple of mouse-clicks.]
[Last Community post: 12-13-2022 @ 9:53AM.]
On Tuesday, December 13, 2022 at 4:06:26 PM UTC-6, John Pollard wrote:
As it turns out, I was right; there is a way to accomplish the original poster's goal using an Excel macro. A subsequent post to the Community Discussion this newsgroup discussion references supplied a link to an earlier Community Discussion with the necessary information.
Users here can read the linked discussion that includes the Excel macro at:
formatting link
But just in case Quicken, Inc. decides, in the future, to switch to new software for the Quicken Community (and destroys the existing links), as Intuit/Quicken have done multiple times in the past; what follows is the Excel macro as supplied by Member mjbank in the linked Community Discussion on 02-16-2020 @ 1:24PM.
GENERAL COMMENTS ABOUT THE MACRO (by the author):
Sub RePopulateRegister() ' Created 20200214 ' License/Copyright: None = Totally free use/copy/modify/share, but USE AT YOUR OWN RISK ' If you feel like it, you can credit author,
formatting link
' Purpose: Fill in partially blank, orphan "sub-rows" on Quicken Register Excel export. ' Why: So this will be a proper spreadsheet that can be sorted on any column ' without orphaning the splits/sub-rows that only have content in the last few columns. ' It endeavors to keep the split/orphan rows together with their parent rows. ' It also allows the user to "pad" the data section of the grid with extra rows and columns at top and sides (i.e., for formulas). ' What: Will populate Date, Account, Num, Description on the blank cells in the orphan sub-rows with data from the parent row. ' The Num column on split/orphan rows will populate with "S*". ' The Description column on the orphan sub-rows will have " SPLIT <NN>" appended to the end (for sorting) ' (<NN> will be like 01, 02, 03, etc.) ' The Description column in the parent row will have " SPLIT 00" appended to the end. ' How: This is not elegant code, it is verbose to make it easier to understand what's happening (see "USE AT YOUR OWN RISK" above!) ' Note: THIS MACRO WILL DELETE THE SUMMARY ROWS BELOW THE DATA ROWS! They would break any sorting/summing you'd want to do. ' Tip: To sort by Date, first sort the Description column (Ascending) and then sort the Date column (in either direction). ' Tip: To enable sort, select header columns Date through Amount and click Excel menu: Data -> Filter ' Caveat: This macro is "brittle" in that it is based on the format of Quicken Register excel exports as of Feb 2020.
THE MACRO (by the author):
First, find the header row within the top 20 rows with "Date", "Account", "Num", "Description" as first few labels... Dim r As Integer, c As Integer, nDone As Integer, rowHdr As Integer, i As Integer Dim stVal As String hdrRow = 0 nDone = 0 r = 1 c = 1 stVal = Cells(r, c).Value Do While (r < 21 And stVal <> "Date") If c > 10 Then c = 1 r = r + 1 Else c = c + 1 End If stVal = Cells(r, c).Value Loop
If Cells(r, c + 1).Value = "Account" And Cells(r, c + 2).Value = "Num" _ And Cells(r, c + 3).Value = "Description" And Cells(r, c + 4).Value = "Memo" _ And Cells(r, c + 5).Value = "Category" And Cells(r, c + 6).Value = "Tag" _ And Cells(r, c + 7).Value = "Clr" And Cells(r, c + 8).Value = "Amount" Then ' We found what appears to be the valid header row; set the column numbers... Dim colDate As Integer, colAcct As Integer, colNum As Integer, colDesc As Integer, colMemo As Integer, colAmount As Integer colDate = c colAcct = c + 1 colNum = c + 2 colDesc = c + 3 colMemo = c + 4 colAmount = c + 8
hdrRow = r Else MsgBox ("Couldn't find a header row with Date, Account, Num, Description, etc.") Exit Sub End If
r = hdrRow + 1
' Now delete any entirely blank rows at the top of the data section under the header row... Do While Application.CountA(Range(Cells(r, colDate), Cells(r, colAmount))) = 0 Cells(r, 1).EntireRow.Delete Loop
' Next, find the last true data row... ' The last data row of the original report is followed by a row with just a date range in the Date column and a total amount in the Amount column. ' So, a valid data row has either a valid Date in the date column or has a blank Date but values in later columns (i.e., not a blank row) Do While isdate(Cells(r, colDate)) Or (Cells(r, colDate).Value = "" And Application.CountA(Range(Cells(r, colDate), Cells(r, colAmount))) > 0) r = r + 1 Loop
' Delete summary rows (assume there won't be more than 20!) For i = 1 To 20 Cells(r, 1).EntireRow.Delete Next
' Now we're ready to proceed... Dim stDate As String, stAcct As String, stNum As String, stDesc As String, stMemo As String Dim stLastDate As String, stLastAcct As String, stLastNum As String, stLastDesc As String, stLastMemo As String Dim dtDate As Date, dtLastDate As Date Dim nSplit As Integer
' Walk remaining rows looking for partially blank "orphan" rows...
r = hdrRow + 1 Do Until Application.CountA(Range(Cells(r, colDate), Cells(r, colAmount))) = 0 ' Collect values of appropriate columns for this row... stDate = Cells(r, colDate).Value stAcct = Cells(r, colAcct).Value stNum = Cells(r, colNum).Value stDesc = Cells(r, colDesc).Value stMemo = Cells(r, colMemo).Value
dtDate = Cells(r, colDate)
If stDate = "" And stAccount = "" And stNum = "" And stDesc = "" Then ' For orphan sub-rows, copy in values from the previous parent row... nDone = nDone + 1 nSplit = nSplit + 1 Cells(r, colDate) = dtLastDate Cells(r, colAcct).Value = stLastAcct Cells(r, colNum).Value = "S*" Cells(r, colDesc).Value = stLastDesc & " SPLIT " & Format(nSplit, "00") If nSplit = 1 Then Cells(r - 1, colDesc).Value = stLastDesc & " SPLIT 00" End If If stMemo = "" Then Cells(r, colMemo).Value = stLastMemo End If
Else ' Otherwise, set the previous values in case the next row is an orphan sub-row... dtLastDate = dtDate stLastAcct = stAcct stLastNum = stNum stLastDesc = stDesc stLastMemo = stMemo nSplit = 0 End If
r = r + 1
Loop
MsgBox ("We re-populated " & nDone & " orphan sub-rows.") End Sub
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.