+ Reply to Thread
Results 1 to 9 of 9

Filter values based on a master list and an input list

  1. #1
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Filter values based on a master list and an input list

    I am trying to copy a spreadsheet i made in google docs in excel keeping the same functionality. Since the formulas are a bit different between the two, i can not simply copy the codes so i must start from scratch. The basics of my spreadsheet is that i have (3) columns: A master list, A filtered list, and an input list. I want the filtered list to change based on the values in the inputted list.

    ColA = Input list
    ColB = Filtered list
    ColC = Master list

    Items:
    Orange
    Red
    Blue
    Yellow
    Green

    ColA contains the colors i manually enter
    ColB contains all the items in ColC that are NOT already listed in ColA (except for Red, i dont what that showing up)
    ColC contains ALL the items above.


    Two Examples of a functional worksheet:

    ColA............ColB
    Orange........Blue
    Green..........Yellow


    ColA........ColB
    Orange....Blue
    .............Yellow
    .............Green

    ColB is where the magic happens, it grows and shrinks based on the values in both the master list ColC and the input list ColA
    Last edited by PY_; 04-11-2013 at 02:57 PM.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter values based on a master list and an input list

    A sample workbook is always useful.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Filter values based on a master list and an input list

    In case my text version is not clear, here is a sample workbook with two examples shown.
    Attached Files Attached Files

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter values based on a master list and an input list

    I use a helper & hidden column with this formula.

    =IF(C10="Red";1;IF(AND(C10<>"";COUNTIF($A$10:$A$15;C10)=0);2))

    Then this ARRAY formula does the job..

    =IF(ISERROR(INDEX($C$10:$C$15;SMALL(IF($D$10:$D$15=2;ROW($C$10:$C$15)-9);ROW(C1))));"";INDEX($C$10:$C$15;SMALL(IF($D$10:$D$15=2;ROW($C$10:$C$15)-9);ROW(C1))))

    =IFERROR(INDEX($C$10:$C$15;SMALL(IF($D$10:$D$15=2;ROW($C$10:$C$15)-9);ROW(C1)));"")
    Attached Files Attached Files

  5. #5
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Filter values based on a master list and an input list

    That worked. I can deal with an hidden column. Thank you Fotis1991

  6. #6
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Filter values based on a master list and an input list

    I am having the hardest time adjusting your sample. Can you remove the two extra charts and move the single chart up to the top of the worksheet so that Row1 has headers and Row2 starts the actual data?

    Row1 = Input Filtered Master
    Row2 = Orange Orange

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter values based on a master list and an input list

    The "KeyPoint" is the red part of the formula.

    =IFERROR(INDEX($C$2:$C$1000;SMALL(IF($D$2:$D$1000=2;ROW($C$2:$C$1000)-1);ROW(C1)));"")
    Attached Files Attached Files

  8. #8
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Filter values based on a master list and an input list

    Thanks! I did change that but i was missing something else. Thanks again for your help, now comes the fun part applying it to my workbook.

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter values based on a master list and an input list

    ...................

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1