Identifying Summary Level Rows in Reports?

wawa
wawa Member
edited April 21 in Reports (Mac)
Is there a column to identify which categories are summary categories and which are lowest level categories?
I'm using a category summary report, then exporting into Excel for further calculations. In the Quicken report, there is a small down arrow (tree) that shows if the category can be expanded into sub-categories, and this information is basically what I'm looking for in the export file. In the Export, I can identify sub-categories by "--" before a subcategory, but this identifier doesn't exist on categories with no subcats.

When I do calculations in Excel, I only want to add the lowest level of categories, whether those exist at level 1, or at level 2 in a tree. But I can't find a way to identify the level. I'm pretty knowledgeable with Excel, so if I need to search and find within fields, that's okay as long as I can automate it.

Best Answer

  • wawa
    wawa Member
    Answer ✓
    Thanks jacobs. It took a few nested ifs to get the result using this formula (LL = "lowest level"; "S" = Summary):

    =IF(B3<>"",IF(OR(LEFT(B4,2)<>" -",(LEFT(B3,2)=" -")),"LL","S"),"S")

    The first string identifies anything with a blank field as a summary (for INCOME, EXPENSE, ADJUSTMENT, TRANSFER)
    The next IF looks if the cell below starts with a " -" (exactly as you described)
    OR
    if the cell itself starts with a " -"

    Now I'm debating between using that or a VLookup with static definition of categories (not my preferred method), because I have a report that includes adjustments and transfers. I sort of want them to fall into another category of "Other". The formula will work for what I need right now. I'll see as time goes by if it does everything I want it to.

    Thanks for your help!

Answers

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    I'm not aware of any identifier to look for to identify categories without subcategories.

    But since you're adept at text formulas in Excel, I think you could create an IF statement based on looking at the cell below the current cell to see if it starts with "--", and then branching appropriately.
    Quicken Mac Subscription • Quicken user since 1993
  • wawa
    wawa Member
    Answer ✓
    Thanks jacobs. It took a few nested ifs to get the result using this formula (LL = "lowest level"; "S" = Summary):

    =IF(B3<>"",IF(OR(LEFT(B4,2)<>" -",(LEFT(B3,2)=" -")),"LL","S"),"S")

    The first string identifies anything with a blank field as a summary (for INCOME, EXPENSE, ADJUSTMENT, TRANSFER)
    The next IF looks if the cell below starts with a " -" (exactly as you described)
    OR
    if the cell itself starts with a " -"

    Now I'm debating between using that or a VLookup with static definition of categories (not my preferred method), because I have a report that includes adjustments and transfers. I sort of want them to fall into another category of "Other". The formula will work for what I need right now. I'll see as time goes by if it does everything I want it to.

    Thanks for your help!
This discussion has been closed.