Dynamically List a Group of Investments?

[FSUDAL]

Hello. I am one solid notch above novice, but a quick learner, and was hoping someone could help get me started with a solution. I have a large list of investments that contain various pieces of information (Name, Ticker, Asset Class, Performance data...). The list itself changes frequently. I would like to have another worksheet dynamically group each investment under the appropriate asset class (Large Cap, Mid Cap, Small Cap). I would also like the list to automatically adjust itself so that there are just enough lines to accommodate the investment list. I have been working with an array formula but ran into a problem since some of the asset class names have variations and I cannot figure out how to create an array formula that allows for several variations (Large Cap, Large-Cap, U.S. Large Cap) that will group under one heading (Large Cap). I would like to get the end result to look something like what you see below and have the blue areas dynamically adjust the number of rows so that there is no extra space between the black headings. I never created a VBA before but if that is the only way to go I am certainly willing to learn! Any help or guidance would be greatly appreciated. Thanks!

Equity

U.S. Equity​

Large Cap
​

Large Cap Investment 1
Large Cap Investment 2
Large Cap Investment 3
​

Mid Cap
​

Mid Cap Investment 1
Mid Cap Investment 2
Mid Cap Investment 3...
​

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

​

 

[tonyyy]

FSUDAL,

Welcome to the Board.

Have you considered trying a PivotTable?

Cheers,

tonyyy

 

[FSUDAL]

Thanks for the quick response tonyyy. I did not consider a pivot table and it sure looks promising. I will have to experiment with this next week and appreciate you pointing me in that direction.

Does the pivot table have the ability to group dissimilar names under the same heading (have Large Cap, Large-Cap, and U.S. Large Cap group under one Large Cap heading)? Otherwise I am assuming that I could add a column to the worksheet that contains all of my data with a very long IF(AND statement that searches for all possible variations and returns the heading name that I want to see in the pivot table (Large Cap in this instance). I say very long, since there will be probably over 100 different asset class names in the raw data, much of which I will want to rename. For example there will be several variations for each of the following categories that I would like to rename so that they group properly;

CASH

FIXED INCOME
Ultra Short Term Bonds
Short Term Bonds
Intermediate Term Bond
Preferreds
High Yield Bond
Inflation Protected Bonds
Developed International Bonds
Emerging Market Bonds

EQUITY
Large Cap Value
Large Cap Blend
Large Cap Growth
Mid Cap Value
Mid Cap Blend
Mid Cap Growth
Small Cap Value
Small Cap Blend
Small Cap Growth
Developed International Value
Developed International Blend
Developed International Growth
Emerging Markets

OTHER
Real Estate
Commodities
Allocation Funds
and about 10 other categories after this...

Is a very long IF(AND statement the best solution to rename about 100 different variations into the category names that I listed above? Each line would need to search for all of the possible variations and return one of the above category names since the data will change frequently. Thanks again!

 

[tonyyy]

PivotTables have a custom text filter capability that can group data based on criteria such as Begins With, Ends With, Contains, Does Not Contain... etc. But I don't think this will work in your situation as you seem to need multiple groups.

Are you able to post a representative sample of your data? (Post your Excel data with Excel Jeanie) And can you also post the category group names (eg, Large Cap, Mid Cap, Small Cap...)?

My thinking is to create a macro that will group/organize the raw data such that you can apply a PivotTable... that is, if you're comfortable with a vba approach.

 

[FSUDAL]

Hi tonyyy. I will try to get something on Excel Jeanie by the end of the week. In the meantime could you explain a little about your idea with organizing the raw data? For example, I have an export from Morningstar that lists the Name, Symbol, Morningstar Category, Equity Style, Bond Style and so on. Some of the categories are not always populated. If I wanted both of the below investments to group under a heading named Large Cap Blend what do you think would be the best way?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Symbol
[/TD]
[TD]Morningstar Category
[/TD]
[TD]Equity Style
[/TD]
[/TR]
[TR]
[TD]3M[/TD]
[TD]MMM[/TD]
[TD][/TD]
[TD]Large Cap Core[/TD]
[/TR]
[TR]
[TD]Vanguard S&P 500[/TD]
[TD]VOO[/TD]
[TD]US Fund Large Blend[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I guess I could also create a new My Heading column and manually assign the correct heading but there's about 780 investments that will change a little each week.

Thanks again!