Results 1 to 10 of 10
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Filters for permutations (Excel 2000/2002)

    I've attached a sample file with the data I'd like to filter. I have features and products whereby any given feature can have up to 4 products that it is found in. I'd like to represent this list such that users can easily select/determine which feature is in which product.

    Feature 1 = Product A
    Feature 2 = Product A,B
    Feature 3 = Product A, B,C
    Feature 4 = Product B
    Feature 5 = Product B, C
    Feature 6 = Product C

    I think I need to re-write the table so that I have multiple feature/product with no repeats but I can't figure it out. I also tried Advanced Filters but got confused there too.

    Any suggestions for the formula-impaired? <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    Thxn, Deb
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Using Filters for permutations (Excel 2000/2002)

    You need to have one line item for each product feature combination. (see sample below, column are separated by commas)

    1, A
    2, A
    2, B
    3, A
    3, B
    3, C etc

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Filters for permutations (Excel 2000/2002)

    Ah, as I suspected <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>, need to separate the fields more. I have about 200 of these rows, is there a formula I can use to separate them out instead of doing it manually? <img src=/S/help.gif border=0 alt=help width=23 height=15>

    Once they are separated, how would I get the filtering like I need?

    Thnx, Deb

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Filters for permutations (Excel 2000/2002)

    For separating, how about

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center valign=bottom>1</td><td align=right valign=bottom></td><td align=right valign=bottom>Product</td><td valign=bottom>A</td><td valign=bottom>B</td><td valign=bottom>C</td><td valign=bottom>D</td><td align=center valign=bottom>2</td><td valign=bottom>Feature 1</td><td valign=bottom>A</td><td valign=bottom>A</td><td valign=bottom> </td><td valign=bottom> </td><td valign=bottom> </td><td align=center valign=bottom>3</td><td valign=bottom>Feature 2</td><td valign=bottom>A,B</td><td valign=bottom>A</td><td valign=bottom>B</td><td valign=bottom> </td><td valign=bottom> </td><td align=center valign=bottom>4</td><td valign=bottom>Feature 3</td><td valign=bottom>A, B, C</td><td valign=bottom>A</td><td valign=bottom>B</td><td valign=bottom>C</td><td valign=bottom> </td><td align=center valign=bottom>5</td><td valign=bottom>Feature 4</td><td valign=bottom>B</td><td valign=bottom></td><td valign=bottom>B</td><td valign=bottom> </td><td valign=bottom> </td><td align=center valign=bottom>6</td><td valign=bottom>Feature 5</td><td valign=bottom>B, C</td><td valign=bottom></td><td valign=bottom>B</td><td valign=bottom>C</td><td valign=bottom> </td><td align=center valign=bottom>7</td><td valign=bottom>Feature 6</td><td valign=bottom>C</td><td valign=bottom> </td><td valign=bottom> </td><td valign=bottom>C</td><td valign=bottom> </td></table>
    where the formula in cell C2, copied right and down, is

    =IF(ISNUMBER(FIND(C$1,$B2)),C$1,"")

    With this kind of layout , you'd need to set filters for Product A in column C, Product B in column D, etc, to match whichever Feature, and it may be necessary to have a lookup table for which Features contain which Products. To filter for Feature 3 you'd need to set:

    Range.AutoFilter Field:=1, Criteria1:="A"
    Range.AutoFilter Field:=2, Criteria1:="B"
    Range.AutoFilter Field:=3, Criteria1:="C"

    Hmm ... (I deal with a similar mess at work and I don't have a good answer!)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using Filters for permutations (Excel 2000/2002)

    You could Filter with Autofilter (no quotes in dialog):
    Contains "A"
    or
    Contains "B"
    Etc

    Or even (this is equivalent)
    equal to "*A*"
    or
    equal to "*B*"
    etc

    You could add a a combobox to select the product and autofilter via a macro.
    I attached a simple example. Is this what you are looking for? It takes the value obtained from the pulldown list and filters accordingly.

    Steve
    Attached Files Attached Files

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Filters for permutations (Excel 2000/2002)

    Yes, that's perfect. I ended up using "Text to Columns" to separate the A,B,C into their own cols. Then I got rid of the commas with =LEFT(C$1,1) and then used Data/AutoFilter. It's not 100% what I wanted but close enough. I like your solution much better although it requires macros (which I'm ok with but some of my end users might not be).

    Thanks to all for your great solutions. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Deb

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Using Filters for permutations (Excel 2000/2002)

    Hi Deb,

    Another approach attached. This one takes your raw data and creates a matrix with it. That on it's own is enough to give a clearer picture of which products have which features. I then added filtering so that you can filter by feature.

    Cheers
    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using Filters for permutations (Excel 2000/2002)

    This one doesn't use macros or filters. The pulldown just gets a "picture" of the features.

    It has some intermediate cols to extract the items from the list.

    Steve
    Attached Files Attached Files

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Filters for permutations (Excel 2000/2002)

    I am using your suggestion for my real data and think I need some kind of wild carding feature, if possible. For example:

    Feature1 = Product "CSC-CE"
    Feature2 = Product "CSC"
    Feature3 = Product "CE"
    Feature4 = Product "ASBR"

    If I use your solution to do a filter/search to show all items with "CSC-CE" I get Feature1, Feature2 and Feature3 when I only want to show Feature1. The other way arouond is also a problem so if I want to only list matches for "CE" I would get Feature1 and Feature3 when I only wnat to show Feature3.

    is there something in the criteria step I can use to tell it 'exact' match?

    Thnx, Deb

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using Filters for permutations (Excel 2000/2002)

    Something more like this. There is a column next to the product name that is looked for in the cells next to the features.

    Steve
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •