MrExcel.com - Your One Stop for Excel Tips & Solutions

Quicken Utilities For Excel

September 24, 2002 - by

The idea for this week's tip came from conversing with Dr. M, author of the great weekly Quicken tips newsletter.

Quicken Copy Command
Quicken Copy Command

I love Quicken, but it certainly has its annoyances. I have a certain memorized report in Quicken with categories down the side and months going across the top. Quicken offers the opportunity to print this report, but of course, I always just use the Copy command to copy the report to the clipboard and then use Edit > Paste in Excel to copy the report to the clipboard. This feature is much faster than the older (and still available) print to a .prn file option.

Here is where the annoyances pop up. First, Quicken did not bother to copy the column headings with the report. So, I manually must enter the month names in Excel. Simple enough. Second, the categories that get copied to the clipboard include an annoying outline format for categories and sub-categories.

Categories and Sub-categories
Categories and Sub-categories

Quicken allows you to use categories and sub-categories to classify your expenses. In the report at left, the automotive expenses are further broken out by insurance, gasoline, repairs and license plates. Perhaps I did a poor job of setting up my categories, but I find that I have some categories where I want to see the subcategory detail and other categories where I would prefer to just see the category total.

I would also like to be able to sort this report in Excel. It would be useful to sort it by total expense, then sort it back by category. Sure, I could use undo, but I would like categories that are alphabetic in nature. In short, I don't care for the outline format used by Quicken.

Long time readers will remember my disdain for the outline format used by pivot tables in Fill in Pivot Table Blank Cells Using Go To Special tip. We have the same situation here. If the Quicken report is merely an intermediate step and you want to be able to sort by category, the outline format is horrible. After sorting by totals and then by category, the Auto:Insurance category will be mis-sorted into the "I" section of the report. For the categories where I keep only the total, they will be incorrectly sorted into the "T" section of the report.

There were two utilities that I thought would ease this situation. Utility one is called collapse. When invoked, this macro will collapse a sub-category into a single line with a proper category name. In the example above, running the macro while the cell-pointer is anywhere in rows 34 through 38 will replace the category in A38 with " Computer" and delete rows 34 through 37.

Utility two is for the categories where I would like to see the subcategory detail, but do not need the heading, the dashed subtotal line, nor the category total. This utility is called Fill. It will find the proper category name, and prefix each subcategory with the cateogry. In the example above, running the macro while the cell-pointer is anywhere in rows 24 through 30 will result in cells A25:A28 being changed to a format like " Auto:Insurance". Rows 24, 29 and 30 will be deleted.

Improved Version of the Report
Improved Version of the Report

At the right, is my improved version of the report. By assigning Collapse and Fill to hot keys, I was able to make these changes with just a few keystrokes. It is now easy to sort the report, knowing that the report can return to it's original sequence by sorting the category.

If you are new to macros, review Introducing the Excel VBA Editor.

Once you copy the macro, you can assign a hot key by following these steps:

  • From the Tools Menu, Choose Macros then Macro
  • Highlight the Fill macro. Click Options. In the Shortcut field, enter any letter. I use f for Fill. Click OK
  • Highlight the collapse macro. Click Options. Pick a letter for a shortcut, but stay away from c, as Ctrl+c is the common shortcut
  • for Edit>Copy. Click OK
  • Close the macro dialog with Cancel.

As part of his quest to design an add-in a day, MrExcel's summer intern, Anhtuan Do created the following macros. 

Option Explicit
Dim Flag, Flag2 As Boolean 'Flags to keep the Find Procedures running
Dim HeaderRow, TotalRow As Integer 'Number of the Header and Total Rows
Dim Counter As Integer 'Counter to ensure searches are relative to activecell
Dim TempString, TempTest As String 'Strings that are used as temporary holders to compare
Dim CategoryName As String 'Name of the category currently in
Sub Collapse()
  'To collapse the rows, run this macro
  Flag = False
  Flag2 = False
  Counter = -1
  'Continue looping until HeaderRow is found
  Do Until Flag = True
    Counter = Counter + 1
    Call FindHeader
  Loop
  
  'Create CategoryName
  CategoryName = Left(CategoryName, Len(CategoryName) - 1)
  CategoryName = Trim(CategoryName)
  'Assign HeaderRow
  HeaderRow = ActiveCell.Row - Counter
  Counter = 0
  
  'Continue looping until TotalRow is found
  Do Until Flag2 = True
    Counter = Counter + 1
    Call FindTotal
  Loop
  
  'Assign TotalRow
  TotalRow = ActiveCell.Row + Counter
  'Replace "TOTAL Category" with "Category"
  Cells(TotalRow, 1).Value = "   " & CategoryName
  'Deleting sub-category rows
  Rows(HeaderRow & ":" & TotalRow - 1).Delete Shift:=xlUp
End Sub

Sub Fill()
  'To add the CategoryName to each of the types, run this macro
  Dim i As Integer
  Flag = False
  Flag2 = False
  Counter = -1
  'Continue looping until HeaderRow is found
  Do Until Flag = True
    Counter = Counter + 1
    Call FindHeader
  Loop
  'Create CategoryName
  CategoryName = Left(CategoryName, Len(CategoryName) - 1)
  CategoryName = Trim(CategoryName)
  'Assign HeaderRow
  HeaderRow = ActiveCell.Row - Counter
  Counter = 0
  'Continue looping until TotalRow is found
  Do Until Flag2 = True
    Counter = Counter + 1
    Call FindTotal
  Loop
  
  'Assign TotalRow
  TotalRow = ActiveCell.Row + Counter
  
  'Adding CategoryName and colon to the start of each sub category
  For i = HeaderRow + 1 To TotalRow - 2
    TempString = Trim(Cells(i, 1).Value)
    Cells(i, 1).Value = "   " & CategoryName & ": " & TempString
  Next i
  
  'Deleting Header and Total Row
  Rows(TotalRow & ":" & TotalRow - 1).Delete Shift:=xlUp
  Rows(HeaderRow).Delete Shift:=xlUp
End Sub

Sub FindHeader()
Dim i As Integer
  TempString = Cells(ActiveCell.Row - Counter, 1).Value
  'Checking to see if in first row
  If (ActiveCell.Row - Counter) = 1 Then
    MsgBox "You are not in a collapsable row"
  End If
  'Checking to see if in a valid row
  'If it reaches a TOTAL before reaching a colon, then error
  If Left(Trim(TempString), 5) = "TOTAL" Then
    MsgBox "You are not in a collapsable row"
  End If
  'Checking the String for a colon
  For i = 1 To Len(TempString)
    TempTest = Mid(TempString, i, 1)
    If TempTest = ":" Then
       CategoryName = TempString
       Flag = True
       Exit For
    End If
  Next i
End Sub

Sub FindTotal()
Dim i As Integer
  'Finding the TOTAL Row
  TempString = Cells(ActiveCell.Row + Counter, 1).Value
  TempString = Trim(TempString)
  If TempString = "TOTAL " & CategoryName Then
    Flag2 = True
  End If
End Sub