Item wise top ten and top hundred total in excel 2007

[tanmanoj]

Dear sir,

i attach excel file in which two sheet one sheet name main sheet have all data and other sheet have desired result which i calculate manually. i want to calculate this through pivot table or other method.

thanks
manoj

[aydeegee]

Hi tanmanoj

I have spent some time looking at your situation and have come to the conclusion that Excel cannot help.

Let us ignore the fact that one cannot get meaningful figures by adding percentages (as in F4:F10).

Most of the data (columns K-M) comes from the main sheet, but then Horticulture, Street light etc appear from somewhere.

Where columns C-E and G-I are different, I cannot see where Excel will get the information.

I have been able to get a pivot table to give the information, but then I noticed that you are on 2003 where the pivot table is materially different to 2010.

Another way of doing this would have been sumifs (sumif is available in 2003 but sumifs did not come in until 2010 (or maybe 2007) )

Because of all this I am unable to help, and I suspect that others are in the same situation.

I have taken time to reply to you in order that you do not think that you have just been ignored.

Regards
Alastair

[tanmanoj]

Dear Alastair

Forgot result sheet can we do one thing in excel automatically.

create sheet automatically with data like sd code wise sheet and with tarrif wise

example

sheet have data only r31 which have only ap meter data and 2 sheet r31 with ds tarrif data is that possible through macro or else
other wise i have to apply filter and copy the data and then paste in another sheet manually.

thanks
manoj

[FDibbins]

Hi and welcome to the forum

1st, the file you uploaded is .xlsx, which means 2007 or later, but your profile indicates 2003 or earlier? Suggest you update your profile?

I can see where some of your totals come from, burt not all, so see if you can apply the following formula to the rest of your data?

In sheet2m C19, copied down, use this...
=SUMIFS('MAIN SHEET'!$D:$D,'MAIN SHEET'!$B:$B,'Rewari Circle Abstract'!B19,'MAIN SHEET'!$C:$C,"R31")

Adjust "'MAIN SHEET'!$D:$D" for the other totals/columns.

you can just use sumif() for the "tota; section...C4:C15.

regarding aydeegee's comment about column F, I assume this was an error on your part, and the formula for F4:F12 was supposed to be the same as for F13 downwards?

[tanmanoj]

Dear Friends

i revised the output sheet please look it at again if that possible through pivot table or sumif function

in the output sheet column are Gross Arrear Current Amount Net Amount

Gross Arrear is ARGROSS

Current amount is (NETAMT-ARGROSS)

Net Amount is NETAMT

Now in top ten defualter mean sum of top 10 argross of tarrif category. Like this other category top 10 and top 100 if possible and this data fetch from oracle database.



Thanks
Manoj