Want to get a Series with Grouping and Sub-Grouping based on 2 criterias.

**e4excel** · 02-01-2011, 03:44 PM

Want to get a Series with Grouping and Sub-Grouping based on 2 criterias.

Dear Forum,

I am tired of getting this done, I have managed to get the Series for one Criteria but I need to get a Sub-series based on 2 criterias as need to get three things done:

1. To get the Series as shown in the COlumn S --. Mode Sr. no

2. And the Sub_mode Sr No based on the different criterias present for each different month as shown in Column O

3. In the Column U I also need to Sum the Premium in the Column M in Monthwise and Modewise..

Ex: - In Cell M2 the premium is Rs 5000/- and the month is june and the mode is QLY so it will be seperate as just Rs 5000/- however, the amounts of Rs 5131 and 1021 are ECs for June so they need to be added.

Regards
e4excel

**MarvinP** · 02-01-2011, 04:24 PM

Hi Vaibhav

When you want to group and subgroup on column S, you would need to have all cells in S filled in. You now have blanks, so it won't sort on those.

I have created a Pivot Table of your data, grouped by Month and then Day. I also have a filter on the Pivot Table using Mode.

I hope this gives you an idea of how Pivot Tables might be used to filter, sort, group and sum values in your table.

**e4excel** · 02-02-2011, 12:59 AM

Thanks MarvinP,

IS it possible to get this done via formulas as the values in the COlumns L,M, N and O are dynamic..

I am going to have formulas in the Columns Z to Column AAD in unison with the values I get in the Column U i.e. Total Amount..

The entire structure is made up of two protions from Column A till Column O it contains all the information of the various policies one has and from Column P till Column AAD the investments to be carried to raise this amount depending on the modes..

Example:

YLY - Cumulative term Deposits
HLY - Half-Year Interest Payments
QLY - Quarterly Interest Payments
ECS - Monthly Interest Payments
SGL - Its already paid..

And therefore the amount for the same Mode and in the same Month needs to be clubbed to get One Amount which can be Invested.

REgards
e4excel

**MarvinP** · 02-02-2011, 02:27 AM

Hi e4excel,
I've looked at your example and don't understand it. I don't see how you are building your table. Perhaps a bigger example would help us understand it better ?

**e4excel** · 02-02-2011, 05:46 AM

MarvinP,

I appreciate you trying to help sincerely..
What I want is to get the series as hsown in the columns S,T and the Total Amount in the Column U..

The Logic is that I have already managed to have a Series based on the mOnths as shown in the column P and in clumn R the Sub-series based on Month..

Now I want that to go to the next level of Mode + Month (Already considered)
Now for the month of June, there are 4 rows of information i.e
21-Jun-2011 Rs 5,000 QLY
21-Jun-2011 Rs 5,000 YLY
28-Jun-2011 Rs 5,131 YLY ( sorry if it was different)
28-Jun-2011 Rs 1,021 ECS

So, it should have the Series for Mode as
1--QLY 1st Unique Mode
2--YLY 2nd Unique Mode
---blank as Unique as there are 2 YLY options as shown above.
3--ECS 3rd Unique Mode

Now again for the other Month July there are only 2 options given as shown below:
25-Jul-2011 Rs 3,586 YLY
25-Jul-2011 Rs 5,060 QLY

So, the numbering would be
1--YLY 1st Unique Mode in the month of July
2--QLY 2nd Unique Mode in the month of July.

SO basically the counter should count the no of Modes in each month and again become 1 for the new month..

Now, for the column T,
we need to look at only 3 Modes appearing in the MOnth of June in which we have
QLY-- 1---as it appears only once
YLY-- 1---Appears twice
YLY-- 2---Appears twice
ECS-- 1--as it appears only once

Hope that helps...