+ Reply to Thread
Results 1 to 4 of 4

Filter list data using only formula

  1. #1
    Registered User
    Join Date
    02-26-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    18

    Filter list data using only formula

    Hi there,



    I need to Filter a range of data based on the respective values in 2 different forms.


    Please help.!!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,853

    Re: Filter list data using only formula

    Hi. Part 1, Design Number (array formula):
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Order Quantity:
    IFERROR(VLOOKUP(A16,$A$4:$B$9,2,FALSE),"")

    Part 2, Design Number (array formula):
    =IFERROR(INDEX($A$4:$A$9,SMALL(IF($B$4:$B$9=B26,ROW($B$4:$B$9)-3,""),1+COUNTIF($B$25:B25,B26))),"")

    Order quantity (array formula):
    =IFERROR(LARGE(IF($B$4:$B$9>0,$B$4:$B$9,""),ROWS($B$26:B26)),"")


    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    Happy to explain, if needed...

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    02-26-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Filter list data using only formula

    Wow.. Thanks. it works well. You are a great guru and magician..

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,853

    Re: Filter list data using only formula

    LoL. Thanks... you're welcome. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Enjoy the holiday season...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Filter data according to dates within data sheet into list box
    By Raylou in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2014, 05:36 AM
  2. best way to filter and extract data from large data list
    By boltonlad2k in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2014, 11:26 AM
  3. Replies: 0
    Last Post: 01-16-2014, 10:55 AM
  4. [SOLVED] Need Data in List box as per the condition filter
    By Naveed Raza in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-27-2013, 11:48 PM
  5. Filter list with Formula
    By BlastRanger in forum Excel General
    Replies: 7
    Last Post: 01-04-2011, 05:42 AM
  6. Filter Data validation List
    By sakinen in forum Excel General
    Replies: 1
    Last Post: 07-05-2010, 08:03 AM
  7. Filter and List Data From Another Sheet
    By Ange Kappas in forum Excel General
    Replies: 7
    Last Post: 03-12-2006, 06:50 PM

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