Identifying Summary Level Rows in Reports?

wawa

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.

wawa

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!

jacobs

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.

wawa

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!