Using Dropdowns to filter Dynamic Array

RICH937

RICH937

Board Regular
Joined
Apr 15, 2023
Messages
59
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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

S
  • Solved
Convert to dynamic array
Replies
7
Views
152
sharshra
S
S
  • Solved
How to sum a column - minus one row , to avoid circular calculations ?
Replies
6
Views
328
StrawberryDreams
S
J
  • Locked
  • Question Question
LET/VSTACK/FILTER question
Replies
6
Views
368
Fluff
Fluff
S
  • Solved
Use dynamic array
Replies
4
Views
158
dreid1011
dreid1011
E
  • Question Question
Exel Filter formula with multiple criterias
Replies
2
Views
277
Fluff
Fluff

Forum statistics

Threads
1,223,872
Messages
6,175,102
Members
452,613
Latest member
amorehouse

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