+ Reply to Thread
Results 1 to 7 of 7

FILTER FUNCTION nested if multi, better/simpler expression?

  1. #1
    Registered User
    Join Date
    11-01-2021
    Location
    Hull, England
    MS-Off Ver
    2021
    Posts
    22

    Talking FILTER FUNCTION nested if multi, better/simpler expression?

    Hello

    XL2021 Database with SEARCH feature in 1st tab, and DATA in 2nd Tab, it is fully working, see attachment.
    And also since recently purchased XL21, wow what a major time saver Excel2021 is, brilliant features close to SQL could have done with this 20 years ago as concept seems simple enough, but anyway in XL21 it is much concise formulas edit and file size, something similar to achieve in Excel 2003 would have been lengthy edit formulas over many tabs, and who knows how many hours/days/weeks/months/years!

    Was wondering if Filter Function expression has simpler alternative, or better way? Even though Filter Function is either AND or OR, anyway!

    Currently 5 seperate formulas via nested if (Col D-A, or Col C-A, or Col B-A, Col A only or Blank Combo Boxes), as clearly seen in screenshot:
    Screenshot (483).png

    Think the 5 seperate formulas (Col A, Col B, Col C, Col D, Blank) as constructed for SEARCH AZ RESULTS A12 spill array is better then 1 nested if as easier to follow/edit at later stage and not get lost somewhere between, look at the 1nest alternative!:

    =UNIQUE(IF($D$2<>"",IF('SEARCH AZ'!A2<>0,(SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)*(IF('SEARCH AZ'!C2<>0,(DATA!C:C='SEARCH AZ'!C2)*(IF('SEARCH AZ'!D2<>"",(DATA!D:D='SEARCH AZ'!D2),"HELLO"))))))),5,1))),IF($C$2<>"",IF('SEARCH AZ'!A2<>0,(SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)*(IF('SEARCH AZ'!C2<>0,(DATA!C:C='SEARCH AZ'!C2)))))),4,1))),IF($B$2<>"",IF('SEARCH AZ'!A2<>0,(SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)))),3,1))),IF($A$2<>"",IF('SEARCH AZ'!A2="","",SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)),2,1)),IF('SEARCH AZ'!A2<>"","",SORT(FILTER(DATA!A:F,(DATA!A:A<>"MAKE")),1,1)))))))


    Cheers Stephan
    Last edited by StephanRS; 03-19-2023 at 05:46 PM. Reason: FILE

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: FILTER FUNCTION nested if multi, better/simpler expression?

    I am not entirely sure what your ultimate goal is but, see if the attached does what you want

    The main changes are such that you can use Searches on 1-4 fields, independently, i.e. you can search exclusively for Fuel Type and return all matches (and DV lists will adjust should you wish to restrict further); in other words, the searches are no longer dependent upon previous fields {so, to search by Type, you don't need to filter by Model, Make and Fuel Type first}.

    To achieve the above, I modified the DV formulae in addition to the table return itself. I also named your 4 search cells to _MAKE, _MODEL, _FUEL and _TYPE respectively.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,932

    Re: FILTER FUNCTION nested if multi, better/simpler expression?

    I also tried something like that (ISNUMBER(SEARCH... and realized that that doesn't quite work. Because it does a "contains" search. If you put in a type of "Tdi", it will bring back both "Tdi" and "Tdi s", which I don't believe the OP wants.

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: FILTER FUNCTION nested if multi, better/simpler expression?

    if an exact match, rather than wildcard, was required then, perhaps something along the lines of:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-01-2021
    Location
    Hull, England
    MS-Off Ver
    2021
    Posts
    22

    Re: FILTER FUNCTION nested if multi, better/simpler expression?

    Quote Originally Posted by XLent View Post
    I am not entirely sure what your ultimate goal is but, see if the attached does what you want

    The main changes are such that you can use Searches on 1-4 fields, independently, i.e. you can search exclusively for Fuel Type and return all matches (and DV lists will adjust should you wish to restrict further); in other words, the searches are no longer dependent upon previous fields {so, to search by Type, you don't need to filter by Model, Make and Fuel Type first}.

    To achieve the above, I modified the DV formulae in addition to the table return itself. I also named your 4 search cells to _MAKE, _MODEL, _FUEL and _TYPE respectively.
    Hello XLENT. That is fabulous! Exactly what I was hoping to achieve, the wild card is definately what I wanted, not exact.
    I can't believe how simple this expression Formula is, easy to edit & understand, and the bonus of totally independent, brilliant, many thanks, but yes my question was to edit 4 combo independent for simpler expression , but this is far better .
    So is this just really formula changes in SRCH TAB>: A12 & DATA TAB>: J2, K2, L2 & M2?

    SRCH A12
    =SORT(FILTER(TableDiv,ISNUMBER(SEARCH(_MAKE,TableDiv[MAKE])
    +SEARCH(_MODEL,TableDiv[MODEL])
    +SEARCH(_FUEL,TableDiv[FUEL])
    +SEARCH(_TYPE,TableDiv[TYPE]))),{1,2,3,4})

    DATA J2
    =SORT(UNIQUE(FILTER(TableDiv[MAKE],ISNUMBER(SEARCH(_MODEL,TableDiv[MODEL])+SEARCH(_FUEL,TableDiv[FUEL])+SEARCH(_TYPE,TableDiv[TYPE])))))

    DATA K2
    =SORT(UNIQUE(FILTER(TableDiv[MODEL],ISNUMBER(SEARCH(_MAKE,TableDiv[MAKE])+SEARCH(_FUEL,TableDiv[FUEL])+SEARCH(_TYPE,TableDiv[TYPE])))))

    DATA L2
    =SORT(UNIQUE(FILTER(TableDiv[FUEL],ISNUMBER(SEARCH(_MAKE,TableDiv[MAKE])+SEARCH(_MODEL,TableDiv[MODEL])+SEARCH(_TYPE,TableDiv[TYPE])))))

    DATA M2
    =SORT(UNIQUE(FILTER(TableDiv[TYPE],ISNUMBER(SEARCH(_MAKE,TableDiv[MAKE])+SEARCH(_MODEL,TableDiv[MODEL])+SEARCH(_FUEL,TableDiv[TYPE])))))

    No vb/macro hidden somewhere? or extra formulas!? If just those 5 edits, wow!

    1 COMMENT. Is it possible for part word search is A2, B2, C2, D2 to appear in DROPDOWN LIST?
    I recognise part word entry works after enter for results in A12, which is a fabulous feature!

    And yes I know Pivot Tables & Slicers can do the same thing but if wanted to derive data/charts from dynamic range I've attached Slicer Pivot version, as well as this formula edit, and surely this is a concise way to do it, a great expression that isn't too complicated if needed to change hdr / range!

    Cheers Stephan

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: FILTER FUNCTION nested if multi, better/simpler expression?

    So is this just really formula changes in SRCH TAB>: A12 & DATA TAB>: J2, K2, L2 & M2?
    Yes but, there is an error/typo in M2 (as provided by me), corrected in red below
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I am not entirely sure I follow the latest request but I will try and take a look at the latest attachments later today.

  7. #7
    Registered User
    Join Date
    11-01-2021
    Location
    Hull, England
    MS-Off Ver
    2021
    Posts
    22

    Talking Re: FILTER FUNCTION nested if multi, better/simpler expression?

    Quote Originally Posted by XLent View Post
    Yes but, there is an error/typo in M2 (as provided by me), corrected in red below
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I am not entirely sure I follow the latest request but I will try and take a look at the latest attachments later today.
    Hello. Many Thanks XLent I've edited file with this formula, and no more rework is required! Fabulous as is thank you .

    This solves the question as after some thought, part word entry & pressing Enter is better/easier (in Type1 file below), then selecting combobox dropdown for list at end (in Type2 file below).

    3 current/final file variations attached:

    TYPE 1 (EXCEL 2021): FILTER Formula IsNumber edit
    4 INDEPENDENT MULTI COMBO BOX (ie A - D in any order, seperate or together) & ALL COMBOBOX with part word search:
    # ComboBox # Filter # IsNumber Independent# 4 Combo AZ###.xlsx

    TYPE 2 (EXCEL 2021): FILTER Formula Unique edit
    4 DEPENDENT MULTI COMBO BOX (ie A > B > C > D) with 1st COMBOBOX part word search:
    # ComboBox # Filter 4MultiDependent AZ Srch XL21##.xlsx

    TYPE 3 (EXCEL 2021):
    PIVOT TABLE & SLICER:
    # PIVOT TBL & SLICER TOTAL INSTRUCT XL21###.xlsx

    PHOTO OF TYPE1 FILE ATTACHED DEMONSTRATING PART WORD ENTRY & ENTRY, IS BETTER THEN ALSO DROPDOWN, WHICH IS ALSO AVAILABLE FOR EXACT MATCHES, PERFECT.
    Screenshot (489).png

    Excel 2021/365:
    Wow best Office applications ever it is fab! The new Excel explosive Cells range all in 1 cell of Formula Function in a SQL esque feature is a massive time saver. Reminds me of the concise accuracy of SumProduct but with criteria of Index Match, but without any of the long term continued ongoing hassle of current, new & different datasets!

    Hence no lots of inevitabe inifinity isolated reading about Cells in web/books (well utube vids these days...), or continously going in backwards trail through other ppls work to see what they did, with vague hope to replicate same data capture/presentation!
    And saves alot of time/effort/knowledge/skill compared to the long hand of ridiclous numerous individual formulas edits, which really was a massive over complication to achieve something simple when an auto filter sub sort was needed/required.

    SLICERS & PIVOT TABLE
    I like this idea and incredibly simply to create/execute in minutes (If Pivot Tbl Layout is in Tabular Format!), and Results are alphanumeric.
    All results AZ without any coding, so messing about with Unique or Sort variations/etiquette.
    Screenshot (490).png
    And also which is very ideal, that raw source data is seperate, in other words left untouched by retards.
    Hence you can leave them to their own devices, advising they can sit there all day, just doing the following for their occassional data needs:
    REFRESH, CLICK (Expand/Collapse), PRESS (Slicer(s)..). Can you hear the coffee vending machine already...

    Many Thanks

    STEPHAN
    Last edited by StephanRS; 03-21-2023 at 05:52 PM.

+ 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. [SOLVED] Multi IF with filter function
    By mazan2010 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-10-2021, 10:36 AM
  2. Simpler formula to handle nested OFFSET function
    By lamlam28 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-18-2019, 03:58 AM
  3. Simpler way than 10+ nested IF statements?
    By jobinv123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-14-2015, 11:40 PM
  4. Replies: 2
    Last Post: 05-22-2013, 05:39 PM
  5. A simpler way to do nested vlookups?
    By bsoper in forum Excel General
    Replies: 4
    Last Post: 01-06-2011, 03:43 PM
  6. Nested IF function with multi AND,OR
    By MAHMUZ in forum Excel General
    Replies: 4
    Last Post: 04-29-2010, 11:01 AM
  7. Multi Nested function formula Error
    By Cullihall in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2008, 10:39 AM

Tags for this Thread

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