Inserting rows in different breakdown sections

[uby]

I have a workbook with two worksheets. The first worksheet has all the information that is going to go in the second sheet. The second sheet is broken down into groups, identified by a 3 letter code. Ex: Group 1 takes all the MCVs. Group 2 takes all the LCVs. And so on. I would like to add as many required rows is needed. The first sheet will change on a monthly basis. It could add a new row or delete a row.

I would like to insert or delete a row if needed. Also, these groups have subtotals at the end of each group. Can anyone help me!!! Pleazzzzzzzzzzzz!

 

[KenWright]

Personally I'd put the identifier into sheet 1 as well and then just use a Pivot table to analyse the results as it will collate all your data just as you have said you need.

http://peltiertech.com/Excel/Pivots/pivotstart.htm

If you also use a dynamic range for your source data fo the pivot table then all you will have to do is to refresh the table each time.

http://www.contextures.com/xlPivot01.html

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[uby]

Ken,

Thanks for the advice. I will try this. I'll let you know if works.

Uby

 

[uby]

Ken,

Unfortunately, it did not work for me because the identifier is a unique code. Do you have any other advice?

Uby

 

[KenWright]

Give us an example of the data you have on sheet1 and then a small example of what you would expect to see on sheet2.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[uby]

Ok....

The first sheet is a list of funds.

For Ex:
IDs Fund Name Shares Ratio So on & so on 50 cols
MVABC ABC Fund
ITDEF DEF Fund
AVGHI GHI Fund

They are all listed in alphabetical order in the first sheet. There are formulas for the totals on this page.

Then...
2nd sheet is broken down by fund type. The first 2 letters of identifies the fund type. Ex: MV - Mutual Value
IT - International Trade, etc.

My 2nd sheet is already setup, headers to separate these fund types. Then after all the MVs, ITs and AVs are brought over, there is a subtotal for each section.

Next month, because this is a monthly report, there might be a fund added or deleted.

I hope I explained it well. Thanks for your response and help.

Uby

 

[KenWright]

No problem from what I see so far. Simply add another column, and it doesn't matter where though personally I'd stick at the front, and assuming your data starts in row1 with headers, and row2 with values, and that your ID column is now col B, in cell A2 put the following:-

=LEFT(B2,2)

Copy that down as far as your data goes and then simply add a header and call it Fund Type.

Now select all your data, do Data / pivot table and chart report, hit Next / Next / Finish.

Drag Fund Type to where it says ROW FIELDS and you will see a unique list of Fund Types down the left, or if you prefer drag it to where it says COLUMN FIELDS and you will see it across the top. Now simply drag whatever field(s) you need into the DATA area and it will all be summarised for you.

You can also if you wish then expand that data by dragging across more fields and nesting them, eg you could drag the ID field between the ROW field that contains Fund Type and the DATA area that contains your values. This would then give you a breakdown of values by Fund Type and by ID.

Given that may be adding/delting data month on monmth then I would definitely take a look at the dynamic data source link I gave you.

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[uby]

Hey Ken!

Unfortunately, the ids consist from 1 digit to 5 digits. Is there some type of function that I can do in order to insert and to get the grand total?

 

[KenWright]

If there is any logic that is used to distinguish between Fund Types / IDs etc then that same logic can be built into a formula that gives you your unique identifier and allows you to group your data in your report.

From the example data you gave it would appear that the first two letters of the ID matched the Fund grouping you refer to, but you say that IDs consist of 1 to 5 digits so I guess I'm struggling to envisage what your data actually looks like.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------