Advanced Filter - Multi Scenario

**TheCardiffGiant** · 08-10-2010, 01:08 PM

Greetings,

I am trying to filter an Excel 2003 spreadsheet. There are two unique scenarios that I need to handle.

First scenario, list contains funds with multiple classes, one of which is called "total," filter the list to return only the fund containing the word "total"

Ex. Scenario 1a
Div Growth - Class A
Div Growth - Class B
Div Growth - Class C
Div Growth - Total
Div Growth Fund

Ex. Scenario 1b
Balanced - Class A
Balanced - Class B
Balanced - Class C
Balanced - Total
Balanced Fund

Expected result (filter and return):
Div Growth - Total
Balanced - Total

In the second scenario, the list does not contain the word/class "Total"

Ex. Scenario 2a
Emerging Mkts Fund - Class A
Emerging Mkts Fund - Class B
Emerging Mkts Fund - Class C
Emerging Mkts Fund

Ex. Scenario 2b
Income - Class H
Income - Class Y
Income - Class X

If the "total" class is not found, but a class(less) version of the fund is found then return the fund with word "fund" in the name [Emerging Mkts Fund].

If the "total" class is not found, and a fund without the word "fund" in the name and where a class is not present is not found, then filter and return the fund with the highest alpha position class [Income - Class H].

Note that all of these funds (and 1,000+ add'l funds) will be in the same column, in the same spreadsheet, so the solution needs to accommodate all of the above situations concurrently.

Thanks very much for the help.

R

**Cheeky Charlie** · 08-10-2010, 01:50 PM

The first is easy, the second is less easy.

The obvious way is to create a reference column which calculates which type a fund is, then filter that.

I can show you how if you upload a small example.

PS, this:

Note that all of these funds (and 1,000+ add'l funds) will be in the same column, in the same spreadsheet, so the solution needs to accommodate all of the above situations concurrently.

Is good news. Too many people chop raw information up then struggle to stick it back together. Far easier to filter a large dataset.

**TheCardiffGiant** · 08-11-2010, 01:58 PM

Cheeky, thanks for the note.

I have attached a file which contains some sample data. Sheet1 contains raw data. Sheet2 contains the expected output; I manually removed rows based on the requirements that I discussed in the previous post.

Note:
Yellow Highlight = Example of Scenario 1a from original post
Orange Highlight = Example of Scenario 1b from original post
Blue Highlight = Example of Scenario 2a from original post
Gray Highlight = Example of Scenario 2b from original post

Thanks again for your help.

R