add hierarchies to excel balance sheet

[RobbyRobbe]

dear all!

I've downloaded a balance sheet of SAP, in with all positions are lined up without hierarchie. In a seperate collum are Hierarchie levels asigned. I'm trying to allocate the hierarchie levels to the balance sheet line items to be able to work with it in a Pivot.

the raw data lists all accounts and sums them up by category.

Normaly the balance sheet should look like that:
assets
- current assets
-- accounts receivables
-- inventories
-- etc.
- fixed assets
- etc

I tried solving the issue via if - and function: =if(and(search("sum*";cell of balance sheet text);cell of hierarchie = 3);right(cell of balance sheet text;lenght(cell of balance sheet text)-4);" ").
This returns an error.
What is wrong about the formula?

When it works, it will return the sums regardless of debit or credit position on the balance sheet. Obviously I could add another column search for debit / credit accounts only. But that would be a lot of columns as there are already 5 hierarchie levels.
Any better ideas?

Many thanks in advance!

 

[Rijnsent]

Hi Robby,
if you want to use your data in a pivot table, you'd have to structure it in such a way that the pivot can take it. And you'd only want values at the lowest level, so your pivot can add them up for you (having subtotals/totals messes that up). That means one column per level. Your sample would translate into:

level1 --- level2 --- level3 --- value
assets --- (empty) --- (empty) --- (no value) -> you could leave this out
assets --- current assets --- (empty) ---(no value) -> you could leave this out
assets --- current assets --- accounts receivables --- a value here (assuming there are no sub values)
assets --- current assets --- inventories --- a value here (assuming there are no sub values)
etc.

So if I put your sample data in cells A2 and down, these are the formulas for
B2 (level of item): =1+LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))
C2 (level1): =IF(LEFT(A2,1)<>"-",A2,B1)
D2 (level2): =IF(LEFT(A2,1)="-",IF(MID(A2,2,1)<>"-",RIGHT(A2,LEN(A2)-2),C1),"")
E2 (level3): =IF(LEFT(A2,2)="--",IF(MID(A2,3,1)<>"-",RIGHT(A2,LEN(A2)-3),D1),"")
Drag them down for the other examples.

Hope that gets you started,

Koen