Using Dropdowns to filter Dynamic Array

RICH937

RICH937

Board Regular
Joined
Apr 15, 2023
Messages
53
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hello Excel People!
I have data in a table that I am trying to transfer over to a dynamic array. There are 4 variables represented by the table columns that I want to sort within the dynamic array using a dropdown for each variable. If there is a value in any of the 4 dropdowns, I am trying to filter the Array with all that are selected. I know there's another way to do this that hides the rows, but I can't go this route as the actual table is +10K long, and it will hide the data I am trying to analyze. I managed to get it to work using what seems like 500 [if(and] formulas, but I am really hoping there is an easier, more compact way of doing this. Applying this formula to the database I'm working on will slow my laptop to a crawl. Also, I have 8 variables in total I'd like to filter/combo filter. I nearly lost my marbles writing this one for 4, and don't think my psyche will stand adding 4 more to this beast.

NOTE: I tried to figure doing this with Lambda, but am absolutely clueless how Lambda works....and help vids are basically non-existent for anything like this.

ANY help will be greatly appreciated! Thanks so much!

Filtered Array Help Please.xlsx
ABCDEFGHIJKLM
1DROPDOWNS
2CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTOR
3→→→→
4
5DATA SETDROPDOWN FILTERED DYNAMIC ARRAY
6CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUMECATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUME
7CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825
8CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710
9CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
10CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
11CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
12CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
13CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
14CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
15CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
16CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
17CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22370
18CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
19CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
20CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
21CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
22CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
23CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
24CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
25CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
26CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
27CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
28
29
Filtered Array Example
Cell Formulas
RangeFormula
H7:L27H7=IF(AND(Dropdowns[CATEGORY]<>"",Dropdowns[CUSTOMER NAME]<>"",Dropdowns[PRODUCT SELECTED]<>"",Dropdowns[DISTRIBUTOR]<>""),FILTER(INDEX(DataSet,,),((DataSet[CATEGORY]=Dropdowns[CATEGORY])*(DataSet[CUSTOMER NAME]=Dropdowns[CUSTOMER NAME])*(DataSet[PRODUCT SELECTED]=Dropdowns[PRODUCT SELECTED])*(DataSet[DISTRIBUTOR]=Dropdowns[DISTRIBUTOR]))),IF(AND(Dropdowns[CATEGORY]="",Dropdowns[CUSTOMER NAME]<>"",Dropdowns[PRODUCT SELECTED]<>"",Dropdowns[DISTRIBUTOR]<>""),FILTER(INDEX(DataSet,,),((DataSet[CUSTOMER NAME]=Dropdowns[CUSTOMER NAME])*(DataSet[PRODUCT SELECTED]=Dropdowns[PRODUCT SELECTED])*(DataSet[DISTRIBUTOR]=Dropdowns[DISTRIBUTOR]))),IF(AND(Dropdowns[CATEGORY]<>"",Dropdowns[CUSTOMER NAME]="",Dropdowns[PRODUCT SELECTED]<>"",Dropdowns[DISTRIBUTOR]<>""),FILTER(INDEX(DataSet,,),((DataSet[CATEGORY]=Dropdowns[CATEGORY])*(DataSet[PRODUCT SELECTED]=Dropdowns[PRODUCT SELECTED])*(DataSet[DISTRIBUTOR]=Dropdowns[DISTRIBUTOR]))),IF(AND(Dropdowns[CATEGORY]<>"",Dropdowns[CUSTOMER NAME]<>"",Dropdowns[PRODUCT SELECTED]="",Dropdowns[DISTRIBUTOR]<>""),FILTER(INDEX(DataSet,,),((DataSet[CATEGORY]=Dropdowns[CATEGORY])*(DataSet[CUSTOMER NAME]=Dropdowns[CUSTOMER NAME])*(DataSet[DISTRIBUTOR]=Dropdowns[DISTRIBUTOR]))),IF(AND(Dropdowns[CATEGORY]<>"",Dropdowns[CUSTOMER NAME]<>"",Dropdowns[PRODUCT SELECTED]<>"",Dropdowns[DISTRIBUTOR]=""),FILTER(INDEX(DataSet,,),((DataSet[CATEGORY]=Dropdowns[CATEGORY])*(DataSet[CUSTOMER NAME]=Dropdowns[CUSTOMER NAME])*(DataSet[PRODUCT SELECTED]=Dropdowns[PRODUCT SELECTED]))),IF(AND(Dropdowns[CATEGORY]<>"",Dropdowns[CUSTOMER NAME]<>"",Dropdowns[PRODUCT SELECTED]="",Dropdowns[DISTRIBUTOR]=""),FILTER(INDEX(DataSet,,),((DataSet[CATEGORY]=Dropdowns[CATEGORY])*(DataSet[CUSTOMER NAME]=Dropdowns[CUSTOMER NAME]))),IF(AND(Dropdowns[CATEGORY]<>"",Dropdowns[CUSTOMER NAME]="",Dropdowns[PRODUCT SELECTED]<>"",Dropdowns[DISTRIBUTOR]=""),FILTER(INDEX(DataSet,,),((DataSet[CATEGORY]=Dropdowns[CATEGORY])*(DataSet[PRODUCT SELECTED]=Dropdowns[PRODUCT SELECTED]))),IF(AND(Dropdowns[CATEGORY]<>"",Dropdowns[CUSTOMER NAME]="",Dropdowns[PRODUCT SELECTED]="",Dropdowns[DISTRIBUTOR]<>""),FILTER(INDEX(DataSet,,),((DataSet[CATEGORY]=Dropdowns[CATEGORY])*(DataSet[DISTRIBUTOR]=Dropdowns[DISTRIBUTOR]))),IF(AND(Dropdowns[CATEGORY]<>"",Dropdowns[CUSTOMER NAME]="",Dropdowns[PRODUCT SELECTED]="",Dropdowns[DISTRIBUTOR]<>""),FILTER(INDEX(DataSet,,),((DataSet[CATEGORY]=Dropdowns[CATEGORY])*(DataSet[DISTRIBUTOR]=Dropdowns[DISTRIBUTOR]))),IF(AND(Dropdowns[CATEGORY]="",Dropdowns[CUSTOMER NAME]<>"",Dropdowns[PRODUCT SELECTED]<>"",Dropdowns[DISTRIBUTOR]=""),FILTER(INDEX(DataSet,,),((DataSet[CUSTOMER NAME]=Dropdowns[CUSTOMER NAME])*(DataSet[PRODUCT SELECTED]=Dropdowns[PRODUCT SELECTED]))),IF(AND(Dropdowns[CATEGORY]="",Dropdowns[CUSTOMER NAME]<>"",Dropdowns[PRODUCT SELECTED]="",Dropdowns[DISTRIBUTOR]<>""),FILTER(INDEX(DataSet,,),((DataSet[CUSTOMER NAME]=Dropdowns[CUSTOMER NAME])*(DataSet[DISTRIBUTOR]=Dropdowns[DISTRIBUTOR]))),IF(AND(Dropdowns[CATEGORY]="",Dropdowns[CUSTOMER NAME]="",Dropdowns[PRODUCT SELECTED]<>"",Dropdowns[DISTRIBUTOR]<>""),FILTER(INDEX(DataSet,,),((DataSet[PRODUCT SELECTED]=Dropdowns[PRODUCT SELECTED])*(DataSet[DISTRIBUTOR]=Dropdowns[DISTRIBUTOR]))),IF(AND(Dropdowns[CATEGORY]<>"",Dropdowns[CUSTOMER NAME]="",Dropdowns[PRODUCT SELECTED]="",Dropdowns[DISTRIBUTOR]=""),FILTER(INDEX(DataSet,,),((DataSet[CATEGORY]=Dropdowns[CATEGORY]))),IF(AND(Dropdowns[CATEGORY]="",Dropdowns[CUSTOMER NAME]<>"",Dropdowns[PRODUCT SELECTED]="",Dropdowns[DISTRIBUTOR]=""),FILTER(INDEX(DataSet,,),((DataSet[CUSTOMER NAME]=Dropdowns[CUSTOMER NAME]))),IF(AND(Dropdowns[CATEGORY]="",Dropdowns[CUSTOMER NAME]="",Dropdowns[PRODUCT SELECTED]<>"",Dropdowns[DISTRIBUTOR]=""),FILTER(INDEX(DataSet,,),((DataSet[PRODUCT SELECTED]=Dropdowns[PRODUCT SELECTED]))),IF(AND(Dropdowns[CATEGORY]="",Dropdowns[CUSTOMER NAME]="",Dropdowns[PRODUCT SELECTED]="",Dropdowns[DISTRIBUTOR]<>""),FILTER(INDEX(DataSet,,),((DataSet[DISTRIBUTOR]=Dropdowns[DISTRIBUTOR]))),DataSet))))))))))))))))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B3:E3List=B$7:B$27
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
How about the below:
Book1
ABCDEFGHIJKL
1DROPDOWNS
2CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTOR
3→→→→CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 2
4
5DATA SETDROPDOWN FILTERED DYNAMIC ARRAY
6CATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUMECATEGORYCUSTOMER NAMEPRODUCT SELECTEDDISTRIBUTORVOLUME
7CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 11825CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
8CHANNEL 1CUSTOMER 1PRODUCT 2DISTRIBUTOR 23710CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
9CHANNEL 1CUSTOMER 2PRODUCT 2DISTRIBUTOR 22900
10CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1840
11CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1760
12CHANNEL 1CUSTOMER 1PRODUCT 1DISTRIBUTOR 1580
13CHANNEL 1CUSTOMER 1PRODUCT 3DISTRIBUTOR 2350
14CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 11490
15CHANNEL 1CUSTOMER 2PRODUCT 3DISTRIBUTOR 21290
16CHANNEL 1CUSTOMER 2PRODUCT 1DISTRIBUTOR 1920
17CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 12370
18CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 21570
19CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 14775
20CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 24160
21CHANNEL 2CUSTOMER 1PRODUCT 3DISTRIBUTOR 12720
22CHANNEL 2CUSTOMER 1PRODUCT 2DISTRIBUTOR 22700
23CHANNEL 2CUSTOMER 1PRODUCT 1DISTRIBUTOR 1690
24CHANNEL 2CUSTOMER 2PRODUCT 3DISTRIBUTOR 2630
25CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2510
26CHANNEL 2CUSTOMER 2PRODUCT 2DISTRIBUTOR 2330
27CHANNEL 2CUSTOMER 2PRODUCT 1DISTRIBUTOR 1320
Sheet1
Cell Formulas
RangeFormula
H7:L8H7=FILTER(Table1, (ISNUMBER(SEARCH(B3,INDEX(Table1,,1))))* (ISNUMBER(SEARCH(C3,INDEX(Table1,,2))))* (ISNUMBER(SEARCH(D3,INDEX(Table1,,3))))* (ISNUMBER(SEARCH(E3,INDEX(Table1,,4)))))
Dynamic array formulas.
 
Upvote 1
Solution
That works perfectly. Amazing. I never even considered isnumber. thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Dynamic Array Formula Problem
Replies
5
Views
124
KRice
K
P
  • Question
Using Dynamic Validation Lists for every row in table
Replies
0
Views
150
psycoperl
P
G
  • Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
350
Greenbehindthecells
G
P
  • Question
Dropdown list not working
Replies
0
Views
177
pyclen
P
G
  • Solved
Combobox search dropdown triggering when editing other sheets
2
Replies
10
Views
550
Akuini
Akuini

Forum statistics

Threads
1,215,071
Messages
6,122,964
Members
449,094
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top