FILTER FUNCTION nested if multi, better/simpler expression?

**StephanRS** · 03-19-2023, 05:41 PM

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

**XLent** · 03-20-2023, 03:36 AM

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.

**Gregb11** · 03-20-2023, 06:18 AM

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.

**XLent** · 03-20-2023, 07:40 AM

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

Formula:

Please Login or Register  to view this content.

**StephanRS** · 03-20-2023, 03:28 PM

Originally Posted by XLent

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

**XLent** · 03-21-2023, 03:22 AM

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:

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.

**StephanRS** · 03-21-2023, 05:19 PM

Originally Posted by XLent

Yes but, there is an error/typo in M2 (as provided by me), corrected in red below

Formula:

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

FILTER FUNCTION nested if multi, better/simpler expression?

LinkBack

Thread Tools

Rate This Thread

Display

FILTER FUNCTION nested if multi, better/simpler expression?

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Multi IF with filter function

Simpler formula to handle nested OFFSET function

Simpler way than 10+ nested IF statements?

If(AND) Nested Function long and complicated. Looking for simpler way if there is

A simpler way to do nested vlookups?

Nested IF function with multi AND,OR

Multi Nested function formula Error

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions