Melissa wrote:
Here is few lines of data

Last Name Relative level Sup last name Credit issued
Center
Smith 0 Penn
1,000,000 WA
Jones 1 Smith
500,000 CHA
Oswald 2 Jones
200,000 CHA
Clark 2 Jones
200,000 CHA
Cortes 3 Clark
10,000 CHA

So for Smith a total of $1 million was given as credit, total of his credits
plus his direct report Jones. Jones gave $500,000 between credits issued by
him and his direct reports Oswald and Clark, and so on. This is just a sample
of the data (trying not to give to much away online since it is
confidential). I have been trying to manipulate the raw data to assist with
the pivot table but just cant get it to go, I tried breaking down each person
by the center they report to, re-organizing the hiearchy piece.


Hi Melissa,

This is an interesting case because of the recursive nature of the
logic. I have been working at reverse-engineering your data to get
amounts per person and have something that works...

I placed your sample data in A1:E6. In G1:I1 I placed values 2, 1, 0.
These are your [Relative level] values, from [max - 1] to 0 (the order
of these values does not matter).

Place in G2 and fill through G2:I6
=$D2-SUMPRODUCT(--($A2=$C$2:$C$7),($D$2:$D$7),--($B$2:$B$7=1+G$1))

The result per person is this formula in H2 (fill down):
=MIN(G2:I2)

What this does... well... ask if you would like an explanation.