FILTER FUNCTION multiple IFs as DYNAMIC RANGE spill? XL2021

[Stephan]

Hello

Worksheet is searchable 1st column az with dynamic multi dependents, in other words data has 4 COMBO BOXES: I2, J2, K2 & L2

Valid data presented using FILTER FUNCTION in I22.



However the question, is correct FILTER FUNCTION to accomodate either 1 COMBO in I2, and/or 2 COMBO in J2, and/or 3 COMBO in K2, and/or 4 COMBO in L2.

Not sure how to combine these 4 cell statements into 1, perhaps there is a better/shorter method in XL2021, but so far these all need merging, that is combining together & display as DYNAMIC SPILL RANGE in I22

4 COMBO: =IF(I2<>0,(SORT(FILTER(A:F,(A:A=I2)*(IF(J2<>0,(B:B=J2)*(IF(K2<>0,(C:C=K2)*(IF(L2<>0,(D:D=L2),"hello"))))))))))
3 COMBO: =IF(I2<>0,(SORT(FILTER(A:F,(A:A=I2)*(IF(J2<>0,(B:B=J2)*(IF(K2<>0,(C:C=K2))))),"hello"))))
2 COMBO: =IF(I2<>0,(SORT(FILTER(A:F,(A:A=I2)*(IF(J2<>0,(B:B=J2))),"hello"))))
1 COMBO: =IF(I2="","",SORT(FILTER(A:F,(A:A=I2))))

Cheers Stephan

 

[Stephan]

Hello

UPDATE: Merged all 4 Dynamic range results from multi dependent Combo Boxes using UNIQUE IF:
=UNIQUE(IF(L2<>"",BT1:BY25,IF(K2<>"",BL1:BQ25,IF(J2<>"",BD1:BI25,IF(I2<>"",AV1:BA25,"")))))

But now the question is... a way to:
MERGE 4 Filter Function Sorts into 1 cell & hence 1 dynamic range, instead of 5! In efficient XL21 / 365.

=IF(I2="","",SORT(FILTER(A:F,(A:A=I2)*(IF(J2<>0,(B:B=J2)*(IF(K2<>0,(C:C=K2)*(IF(L2<>0,(D:D=L2))))))))))

=IF(I2="","",SORT(FILTER(A:F,(A:A=I2)*(IF(J2<>0,(B:B=J2)*(IF(K2<>0,(C:C=K2))))))))

=IF(I2="","",SORT(FILTER(A:F,(A:A=I2)*(IF(J2<>0,(B:B=J2))))))

=IF(I2="","",SORT(FILTER(A:F,(A:A=I2))))

UPDATED files attached (1st picture with workings, and 2nd picture version is SEARCH tab & DATA tab to make it obvious what is source data & results!).
All works, but ideally surely there is short hand version that works exclusively in 21 or 365.

 

[Stephan]

Hello

After some thought since FILTER FUNCTION is either AND or OR, it is likely it best expressed as I've edited it!

It is easy to follow Filter Formula Dynamic Boxes A-D + Blank (P1, X1,AF1,AN1, AV1) as seperates like this, but perhaps there is an even simpler solution!:

DYN 1BOX (A)	DATA	P1	=IF('SEARCH AZ'!A2="","",SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)),2,1))
DYN 2BOX (B)	DATA	X1	=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)))
DYN 3BOX (C)	DATA	AF1	=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)))
DYN 4BOX (D)	DATA	AN1	=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)))
DYN BLANK	DATA	AV1	=IF('SEARCH AZ'!A2<>"","",SORT(FILTER(DATA!A:F,(DATA!A:A<>"MAKE")),1,1))
SRCH DYN IF	SRCH	A12	=UNIQUE(IF(D2<>"",DATA!AN1:AS25,IF(C2<>"",DATA!AF1:AK25,IF(B2<>"",DATA!X1:AC25,IF(A2<>"",DATA!P1:U25,(DATA!AV1:BA25))))))
SRCH NO ZERO	SRCH	A12	SELECT SEARCH RESULTS BELOW HDR: FORMAT CELLS > CUSTOM: 0;-0;;@

Instead of the Nested If alternative of the 4 Dynamic Box selections + if Blank selected, as less obvious of what equals intended!

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)))))))

Revised file with smart unique/sort edits attached as 2 Tab: SEARCH AZ & DATA.

 

[p45cal]

Something along these lines? (You can wrap the whole thing in a sort etc.):

=FILTER(TableDiv,
(
IF(I2<>0,TableDiv[MAKE]=I2,SEQUENCE(ROWS(TableDiv),,1,0))*
IF(J2<>0,TableDiv[MODEL]=J2,SEQUENCE(ROWS(TableDiv),,1,0))*
IF(K2<>0,TableDiv[FUEL]=K2,SEQUENCE(ROWS(TableDiv),,1,0))*
IF(L2<>0,TableDiv[TYPE]=L2,SEQUENCE(ROWS(TableDiv),,1,0))
))

 

[Stephan]

Something along these lines? =FILTER(TableDiv, etc...

Hello! This sort of works.... and sort of doesn't... for correct Combo Box selection.

HOWEVER on COL B selection does NOT select correct data just the top most entries.
For example select MAKE: BMW, MODEL: X4, you'll notice 3 SERIES is wrongly displayed in results data.
So...... how to pair... SEQUENCE A+B, A+B+C, A+B+C+D.... think it looks at it currently wrongly, like A then B then C then D, not all paired up n that..

See in file attached, temp position in SEARCH AZ A12 as comparison to original edit:

=UNIQUE(SORT(FILTER(TableDiv,
(
IF(A2<>0,TableDiv[MAKE]=DATA!J2,SEQUENCE(ROWS(TableDiv),,1,0))*
IF(B2<>0,TableDiv[MODEL]=DATA!K2,SEQUENCE(ROWS(TableDiv),,1,0))*
IF(C2<>0,TableDiv[FUEL]=DATA!L2,SEQUENCE(ROWS(TableDiv),,1,0))*
IF(D2<>0,TableDiv[TYPE]=DATA!M2,SEQUENCE(ROWS(TableDiv),,1,0))
)),2,1))

And another minus...
HOWEVER if no COMBO BOXES are selected, it does display all fields but not in alphabetical order, the order they appear in the source data.

QUESTION: Any ideas how to edit above alphanumeric order for blank selections (ie display all fields of data)?
I added in unique & sort functions, but for no selections in Combo A+B+C+D as already said, appear in order as per original data, but having said that surprised displays anything.

Cheers Stephan

 

[p45cal]

In cell A12 of the SEARCH AZ sheet, why are you referring to cells J2, K2, L2 M2 on the DATA sheet?



I think you should be looking at cells A2:D2 of the same sheet:



In the above formulae you've been sorting the results by column 2, here's the way to sort by column 1, then by column 2:

 

[p45cal]

I have another suggestion; a lot of what you're doing is built-in to Excel.
Take a look at Sheet2 of the attached, it's a standalone sheet, it doesn't have any formulae in. It's your table with 4 added slicers:

Nothing chosen (each slicer is already sorted):


Click on Peugeot, and immediatel;y you'll see a change in all the slicers; the Model slicer shows what's remaining to choose from at the top, with other options that would result in everything being filtered out greyed out.



You're not stuck with just making a single selection from each slicer (the usual keyboard combinations (Ctrl or Shift while clicking) apply (or you can use the multiselect icon)):

Clear all selections from a slicer with the Clear Filter icon:


Nor do you have to make your choices left to right; make a choice in any slicer in any order. Here I chose Diesel first, then Ford and Peugeot:



Down sides:
It's a plain filtered table which hides rows so anything on the sheet to the right of the table will also have rows hidden.
There's no sorting or unique filtering.
If the table was sorted before you filtered anything that would take care of that, but the unique aspect would need some ingenuity, as would getting a list with no hidden rows. I doubt I'll get time to think about that one today.

The attached also includes the change suggested in my previous message. (edit: limit of 5 attachments reached, see next msg for attachment)

 

[p45cal]

The attachment

ps. Know the Haworth?, or the Gardeners Arms?

 

[Debaser]

You might also want to note that the cross-posting rules are the same here as elsewhere.

 

[Stephan]

HI.

SEQUENCE formula: Inconsistent results, save 20kb file it then 22mb? Perhaps it works fine on XL365? XL21 on laptop gets confused with it.

SLICERS: Absolutely why mess about with formulas when this feature can do it all with you. Only reason is I did formula approach is can never get the Pivot Table layout how I want it to resemble the raw source data layout, and XL21 is a recent purchase and wanted to get to grips with this ultra concise Filter Function formula, impressive stuff, makes me wonder why this SQL esque stuff wasn't available 20 years ago...

Appreciate your clear explainations and effort. I rarely do anything with XL, except what intrigues me.

PUBS: Yes pal they are all here in HULL near UNIVERSITY & Newland Av which is popular with bars/restaurants these days:
Haworth hasn't changed in 20 years! Always popular with students.
Gardeners Arms, they've done alot with it over the years, again fond memories of student life here at Uni, & in summer the front beer garden is well busy.

 

[Stephan]

You might also want to note that the cross-posting rules are the same here as elsewhere.

CROSS POSTS:

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...

www.excelforum.com

Lots of options!

FILTER FUNCTION for Multi Dependent simpler/better expressio...

Forum

www.myonlinetraininghub.com

Slicer

 

[Stephan]

Hello

Thanks for suggesting PIVOT TABLE SLICERS. My final file attached, no formulas or vb, hence no ridicoulous over complicated feat with offset indirect lookup index match etc, no messing about with UNIQUE or SORT to display AZ, just SOURCE DATA, PIVOT TABLE & SLICERS, hooray!
These PivotTables & Slicers really only takes a few minutes with already populated source data, and no numerous edits filter function to get mass spill cell explosion right.. But for me the confusion has been around the layout for many many many years, but that's all changed..

As mentioned previously Pivot Table layout has been irratating me for absolutely years, and couldn't get the settings right as per normal layout, but inspired by XL2021 today finally figured out the right option...... TABULAR FORMAT!

Apparently I'd not noticed for years, as always right clicked Pivot Table looked through settings, only to find few options, that made little/no difference to what I wanted, and I must have looked at Field Settings and Fields List who knows how often, then today noticed in MENU HDR options that PIVOT TABLE ANALYSE & DESIGN appear when PIVOT TABLE is selected...... Well eventually noticed the emerging options, finally many years later... Have to admit never thought I'd actually get to the bottom of what was needed to setup it up right, but here it is........

TABULAR FORMAT: Select PIVOT TABLE > DESIGN (HDR MENU)> REPORT LAYOUT > TABULAR.

SUB/GRANDTOTALS: Select PIVOT TBL > DESIGN (HDR MENU) > SUB/GRAND TOTALS > OFF / DO NOT.

And not to go searching through those options wondering what was it every time, there's an option to set Tabular as default Pivot Table layout!

DEFAULT PIVOT TABLE LAYOUT: FILE, MORE, OPTIONS, DATA, EDIT DEFAULT LAYOUT, REPORT, TABULAR



So thanks for all the suggestions, what it came down to, was the confusing default pivot table layout, the rest is basic simple stuff really, and so simple no vb or formulas needed, small file size too, this is well under 100kb, which is less 0.1MB which is nothing!

Cheers Stephan

 

[Stephan]

Hello, having just said how easy Pivot Tables & Slicers (Once familiar with where TABULAR LAYOUT option is hiding!) are, and without any coding in Formula....

Well back to my original question, I wanted a simpler expression Formula edit for the 4Multi-dependent COMBO BOX, well....

In another Forum cross post:

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...

www.excelforum.com

Their Forum expert came up with exactly that, and more! Just 5 formula edits, and 4 COMBO BOXS work independently, with part word entry on enter, fabuous! File attached, workings below:
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])))))

Cheers Stephan

 

[Stephan]

Hello. Consider these questions solved! The simpler expression for MultiComboBox FILTER Formula is done with IsNumber edit, as is consistent/reliable when compared with Sequence, at least on my i3 Laptop with Excel 2021.

3 current/final file variations available here @ cross post:

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...

www.excelforum.com

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.

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.
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...

Cheers

STEPHAN

 

[Stephan]

I have another suggestion; a lot of what you're doing is built-in to Excel.
Take a look at Sheet2 of the attached, it's a standalone sheet, it doesn't have any formulae in. It's your table with 4 added slicers:

Nothing chosen (each slicer is already sorted):
View attachment 83476

Click on Peugeot, and immediatel;y you'll see a change in all the slicers; the Model slicer shows what's remaining to choose from at the top, with other options that would result in everything being filtered out greyed out.

View attachment 83477

You're not stuck with just making a single selection from each slicer (the usual keyboard combinations (Ctrl or Shift while clicking) apply (or you can use the multiselect icon)):
View attachment 83478
Clear all selections from a slicer with the Clear Filter icon:
View attachment 83479

Nor do you have to make your choices left to right; make a choice in any slicer in any order. Here I chose Diesel first, then Ford and Peugeot:

View attachment 83480

Down sides:
It's a plain filtered table which hides rows so anything on the sheet to the right of the table will also have rows hidden.
There's no sorting or unique filtering.
If the table was sorted before you filtered anything that would take care of that, but the unique aspect would need some ingenuity, as would getting a list with no hidden rows. I doubt I'll get time to think about that one today.

The attached also includes the change suggested in my previous message. (edit: limit of 5 attachments reached, see next msg for attachment)

Hello!

SLICERS are big time saver, using CAMERA Command can use same SOURCE (DATA TAB) in SLICERS tab, & SEARCH tab!
It is a simple Trick but saves having duplicate sources! Attached current sample files, 3 types, Formula variations of Spill in either SEQUENCE, SEARCH (These 2 are any order searches), or UNIQUE (Whereas this 1 is in following order only).



Also I'm looking for simpler expression of the following, it is the formula in DATA (TAB) pre the 4 Multi Combo in SEARCH AZ (TAB), it functions so that selections via combo can be in any independent order.

4 Fields > MAKE, MODEL, FUEL, TYPE, currently how I make sence is 1234, depending on which Field (ie that goes 1st), for example if: MODEL (2134). If that makes zero sence, see SEARCH AZ (TAB), COLUMNS: H I

ISNUMBER FILTER COMBO

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

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

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

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