sum sub levels.

Jerome · 03-30-2006, 10:40 AM

Hi there,

My name is Jerome. I would appreciate your help with the following problem.
I have an excel sheet whcih contains data displayed in various levels. There
are 11 levels in total. There are over 5000 rows. However each level has
different categories. I have to add the values of level 2 in level 1 and all
the values of level 3 into 2 and so forth. See tteh format below.

You'll notice that I have to add (PAYMENTS, TRANSFERS AND SUBSIDIES,
PAYMENTS FOR CAPITAL ASSETS - which are in level 2 ) in level 1 (PAYMENTS)

I should do the same for (TAX RECEIPTS, SALES GOODS & SERV NON CAP ***,
TRANSFERS RECEIVED, FINES, PENALTIES AND FORFEITS, INTEREST, DIVIDEND, RENT
ON LAND, SALES OF CAPITAL ASSETS) and add it in level 1 (RECEIPTS)

LEVEL CODE AMOUNT 1 2
1 96623 PAYMENTS
2 982623 PAYMENTS
2 988623 TRANSFERS AND SUBSIDIES
2 996623 PAYMENTS FOR CAPITAL ASSETS
1 6318623 DIRECT EXCHEQUER PAYMENTS
2 6319623 DIRECT EXCHEQUER PAYMENTS
1 1623 RECEIPTS
2 2623 TAX RECEIPTS
2 3623 SALES GOODS & SERV NON CAP ***
2 40623 TRANSFERS RECEIVED:
2 65623 FINES, PENALTIES AND FORFEITS
2 72623 INTEREST, DIVIDEND, RENT ON LAND
2 102623 SALES OF CAPITAL ASSETS

I have to do this for the entire worksheet. Could you please advise me how I
should go about this.

Thank you very much!:O)

Best regards,

Jerome

Bernard Liengme · 03-30-2006, 04:30 PM

I do not understand your 'levels'. Tell us how the data is set out: column
A as level-number 1 to 12? Column B has amount, and C has item name?
What you want to add?
All Level 1 Payments, all level 2 payments - as 2 separate numbers or 1 ?

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Jerome" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
>
> My name is Jerome. I would appreciate your help with the following
> problem.
> I have an excel sheet whcih contains data displayed in various levels.
> There
> are 11 levels in total. There are over 5000 rows. However each level has
> different categories. I have to add the values of level 2 in level 1 and
> all
> the values of level 3 into 2 and so forth. See tteh format below.
>
> You'll notice that I have to add (PAYMENTS, TRANSFERS AND SUBSIDIES,
> PAYMENTS FOR CAPITAL ASSETS - which are in level 2 ) in level 1 (PAYMENTS)
>
> I should do the same for (TAX RECEIPTS, SALES GOODS & SERV NON CAP ***,
> TRANSFERS RECEIVED, FINES, PENALTIES AND FORFEITS, INTEREST, DIVIDEND,
> RENT
> ON LAND, SALES OF CAPITAL ASSETS) and add it in level 1 (RECEIPTS)
>
> LEVEL CODE AMOUNT 1 2
> 1 96623 PAYMENTS
> 2 982623 PAYMENTS
> 2 988623 TRANSFERS AND SUBSIDIES
> 2 996623 PAYMENTS FOR CAPITAL ASSETS
> 1 6318623 DIRECT EXCHEQUER PAYMENTS
> 2 6319623 DIRECT EXCHEQUER PAYMENTS
> 1 1623 RECEIPTS
> 2 2623 TAX RECEIPTS
> 2 3623 SALES GOODS & SERV NON CAP ***
> 2 40623 TRANSFERS RECEIVED:
> 2 65623 FINES, PENALTIES AND FORFEITS
> 2 72623 INTEREST, DIVIDEND, RENT ON LAND
> 2 102623 SALES OF CAPITAL ASSETS
>
> I have to do this for the entire worksheet. Could you please advise me how
> I
> should go about this.
>
> Thank you very much!:O)
>
> Best regards,
>
> Jerome
>

Jerome · 03-31-2006, 02:00 AM

Good morning Bernhard,

Thank you for your response. I need to add all the amounts (that column is
not shown in my request) for in each level (except level 1 of course) and
place the total in the level immediately above it.

Col 1 as Lvl (has levels 1 - 11)
Col 2 as "Code" (Unique values)
Col 3 as "Amount" (values will be entered in here)
Col 4 as "Total" (This is where the formula will be entered)

There are over 5000 rows.
lvl code desc total amount 1 2 3 4
1 6333623 revenue fund receipts revenue fund receipts
2 6338623 debt portfolio debt portfolio
3 6351623 dbl pmt r150 settlemnt 21/7/00 dbl pmt r150 settlemnt 21/7/00
3 6351623 dbl pmt r150 settlemnt 21/7/00 dbl pmt r150 settlemnt 21/7/00
3 6350623 prem on debt protfolio restruct prem on debt protfolio restruct
3 6350623 prem on debt protfolio restruct prem on debt protfolio restruct
3 6349623 premium on issuance of bonds premium on issuance of bonds
3 6349623 premium on issuance of bonds premium on issuance of bonds
3 6348623 profit on conv of foreign loans profit on conv of foreign loans
3 6348623 profit on conv of foreign loans profit on conv of foreign loans
2 6335623 fines, penalties and forfeits fines, penalties and forfeits
3 6339623 penalties penalties
4 6341623 amnesty unit amnesty unit
4 6341623 amnesty unit amnesty unit
4 6340623 retail bonds retail bonds
4 6340623 retail bonds retail bonds

Yes, i want to add the levles as two seperate numbers, however there are
many different categories of levels for instance level 1 (Revenue) and level
1 (Direct Payments) etc ... each with their own sublevels. The sheet is
structured so that the sublevels (which need to be added for the level above
it) are immediately below the level above it.

I sincerely hope this explains it more clearly

"Bernard Liengme" wrote:

> I do not understand your 'levels'. Tell us how the data is set out: column
> A as level-number 1 to 12? Column B has amount, and C has item name?
> What you want to add?
> All Level 1 Payments, all level 2 payments - as 2 separate numbers or 1 ?
>
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Jerome" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there,
> >
> > My name is Jerome. I would appreciate your help with the following
> > problem.
> > I have an excel sheet whcih contains data displayed in various levels.
> > There
> > are 11 levels in total. There are over 5000 rows. However each level has
> > different categories. I have to add the values of level 2 in level 1 and
> > all
> > the values of level 3 into 2 and so forth. See tteh format below.
> >
> > You'll notice that I have to add (PAYMENTS, TRANSFERS AND SUBSIDIES,
> > PAYMENTS FOR CAPITAL ASSETS - which are in level 2 ) in level 1 (PAYMENTS)
> >
> > I should do the same for (TAX RECEIPTS, SALES GOODS & SERV NON CAP ***,
> > TRANSFERS RECEIVED, FINES, PENALTIES AND FORFEITS, INTEREST, DIVIDEND,
> > RENT
> > ON LAND, SALES OF CAPITAL ASSETS) and add it in level 1 (RECEIPTS)
> >
> > LEVEL CODE AMOUNT 1 2
> > 1 96623 PAYMENTS
> > 2 982623 PAYMENTS
> > 2 988623 TRANSFERS AND SUBSIDIES
> > 2 996623 PAYMENTS FOR CAPITAL ASSETS
> > 1 6318623 DIRECT EXCHEQUER PAYMENTS
> > 2 6319623 DIRECT EXCHEQUER PAYMENTS
> > 1 1623 RECEIPTS
> > 2 2623 TAX RECEIPTS
> > 2 3623 SALES GOODS & SERV NON CAP ***
> > 2 40623 TRANSFERS RECEIVED:
> > 2 65623 FINES, PENALTIES AND FORFEITS
> > 2 72623 INTEREST, DIVIDEND, RENT ON LAND
> > 2 102623 SALES OF CAPITAL ASSETS
> >
> > I have to do this for the entire worksheet. Could you please advise me how
> > I
> > should go about this.
> >
> > Thank you very much!:O)
> >
> > Best regards,
> >
> > Jerome
> >
>
>
>

**starguy** · 03-31-2006, 02:51 AM

can you send sample data of that workbook

>>[email protected]

**John James** · 03-31-2006, 04:30 AM

Hi Jerome,

How I'd approach the problem given the info provided, if the objective was minimising the effort to produce the result:

First step is to get all of your posting level accounts and amounts and relevant identifiers into a simple, well-structured datasheet. This source data should have a separate row for each posting level account and no rows for any summary level accounts.

In this datasheet, the relevant summary level accounts for each level, can be listed against each row within a separate column. e.g. The relevant level 2 summary level accounts for each posting account are listed in a "Level 2" column. This ensures that every amount has every necessary identifier on that same row to enable summary level totals to be calculated. These summary level account cells are likely to be populated with vlookup formulae which extract from a separate master account table (which shows all summary account descriptions at all levels for all posting accounts) - or, easier still (if possible), are included on each row in the extract from your ledger.

Once you have your data in a database like this, production of a report may be relatively straight-forward, depending upon how specific the final report format needs to be. I'd suggest you look at Data-SubTotals to produce a report similar to your description.

Good luck,

David McRitchie · 04-01-2006, 10:35 PM

Hi Jerome,
So how many levels do you actually have and how do you intend to separate
Level 2 totals from level 3 totals, perhaps with color, but are there more levels
than 3 you now show. I did figure out that you had an amount column, but you should be able
to define your problem for others, especially since it will require one to write a
macro to produce your format after you insert/delete rows. .

"Jerome" <[email protected]> wrote
> Thank you for your response. I need to add all the amounts (that column is
> not shown in my request) for in each level (except level 1 of course) and
> place the total in the level immediately above it.
>
> Col 1 as Lvl (has levels 1 - 11)
> Col 2 as "Code" (Unique values)
> Col 3 as "Amount" (values will be entered in here)
> Col 4 as "Total" (This is where the formula will be entered)
>
> There are over 5000 rows.
> lvl code desc total amount 1 2 3 4
> 1 6333623 revenue fund receipts revenue fund receipts
> 2 6338623 debt portfolio debt portfolio
> 3 6351623 dbl pmt r150 settlemnt 21/7/00 dbl pmt r150 settlemnt 21/7/00
> 3 6351623 dbl pmt r150 settlemnt 21/7/00 dbl pmt r150 settlemnt 21/7/00
> 3 6350623 prem on debt protfolio restruct prem on debt protfolio restruct
> 3 6350623 prem on debt protfolio restruct prem on debt protfolio restruct
> 3 6349623 premium on issuance of bonds premium on issuance of bonds
> 3 6349623 premium on issuance of bonds premium on issuance of bonds
> 3 6348623 profit on conv of foreign loans profit on conv of foreign loans
> 3 6348623 profit on conv of foreign loans profit on conv of foreign loans
> 2 6335623 fines, penalties and forfeits fines, penalties and forfeits
> 3 6339623 penalties penalties
> 4 6341623 amnesty unit amnesty unit
> 4 6341623 amnesty unit amnesty unit
> 4 6340623 retail bonds retail bonds
> 4 6340623 retail bonds retail bonds
>
> Yes, i want to add the levles as two seperate numbers, however there are
> many different categories of levels for instance level 1 (Revenue) and level
> 1 (Direct Payments) etc ... each with their own sublevels. The sheet is
> structured so that the sublevels (which need to be added for the level above
> it) are immediately below the level above it.
>
> I sincerely hope this explains it more clearly
>
> "Bernard Liengme" wrote:
>
> > I do not understand your 'levels'. Tell us how the data is set out: column
> > A as level-number 1 to 12? Column B has amount, and C has item name?
> > What you want to add?
> > All Level 1 Payments, all level 2 payments - as 2 separate numbers or 1 ?
> >
> >
> > --
> > Bernard V Liengme
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "Jerome" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi there,
> > >
> > > My name is Jerome. I would appreciate your help with the following
> > > problem.
> > > I have an excel sheet whcih contains data displayed in various levels.
> > > There
> > > are 11 levels in total. There are over 5000 rows. However each level has
> > > different categories. I have to add the values of level 2 in level 1 and
> > > all
> > > the values of level 3 into 2 and so forth. See tteh format below.
> > >
> > > You'll notice that I have to add (PAYMENTS, TRANSFERS AND SUBSIDIES,
> > > PAYMENTS FOR CAPITAL ASSETS - which are in level 2 ) in level 1 (PAYMENTS)
> > >
> > > I should do the same for (TAX RECEIPTS, SALES GOODS & SERV NON CAP ***,
> > > TRANSFERS RECEIVED, FINES, PENALTIES AND FORFEITS, INTEREST, DIVIDEND,
> > > RENT
> > > ON LAND, SALES OF CAPITAL ASSETS) and add it in level 1 (RECEIPTS)
> > >
> > > LEVEL CODE AMOUNT 1 2
> > > 1 96623 PAYMENTS
> > > 2 982623 PAYMENTS
> > > 2 988623 TRANSFERS AND SUBSIDIES
> > > 2 996623 PAYMENTS FOR CAPITAL ASSETS
> > > 1 6318623 DIRECT EXCHEQUER PAYMENTS
> > > 2 6319623 DIRECT EXCHEQUER PAYMENTS
> > > 1 1623 RECEIPTS
> > > 2 2623 TAX RECEIPTS
> > > 2 3623 SALES GOODS & SERV NON CAP ***
> > > 2 40623 TRANSFERS RECEIVED:
> > > 2 65623 FINES, PENALTIES AND FORFEITS
> > > 2 72623 INTEREST, DIVIDEND, RENT ON LAND
> > > 2 102623 SALES OF CAPITAL ASSETS
> > >
> > > I have to do this for the entire worksheet. Could you please advise me how
> > > I
> > > should go about this.
> > >
> > > Thank you very much!:O)
> > >
> > > Best regards,
> > >
> > > Jerome
> > >
> >
> >
> >