Filter data by multiple independent dropdowns for multiple columns

K

KatRum

New Member
Joined
Nov 1, 2018
Messages
11
Hi All,

I received a really good response from here before and I am hoping someone can help me again. I browsed for an answer but it does not exist so far. I'll explain what I am trying to do and also what I have so far.

I have a large table with a lot of data. I have some dropdowns in B2:B9 which correspond with the data in columns DY:EF in the respective order. I would like the dropdowns to filter the data but only in the respoective field. People will only use one dropdown at a time so they don't have to interract with each other. I try to demonstrate below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]DY[/TD]
[TD]DZ[/TD]
[TD]EA[/TD]
[TD]EB[/TD]
[TD]EC[/TD]
[TD]ED[/TD]
[TD]EE[/TD]
[TD]EF[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lot 1[/TD]
[TD]Lot 2[/TD]
[TD]Lot 3[/TD]
[TD]Lot 4[/TD]
[TD]Lot 5[/TD]
[TD]Lot 6[/TD]
[TD]Lot 7[/TD]
[TD]Lot 8[/TD]
[/TR]
[TR]
[TD]Company 1[/TD]
[TD]Apples
Oranges
Kiwis[/TD]
[TD]Sofas
Armchairs
Tables
Chairs[/TD]
[TD][TABLE="width: 80"]
<colgroup><col width="80" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 80"]Cups
Teapots[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 80"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 80"][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 80"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Desktop
Laptop
Mouse[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Kat
Sarah
Mike
Gemma[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Willow
Oak[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Scaffolding
Roofers
[/TD]
[TD]Peas
Broccoli
Parsnip
Tomato
Potato
[/TD]
[/TR]
[TR]
[TD]Company 2[/TD]
[TD]Apples
Kiwis[/TD]
[TD]Chairs
Tables
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Teapots
Coffee[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Mouse
Keyboard
Mobile[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Kat
Gemma[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Plumbers
Scaffolding
Electricians[/TD]
[TD]Potato
Parsnip
Tomato[/TD]
[/TR]
[TR]
[TD]Company 3[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Tea
Coffee
Teapots[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]Mobile[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Sam
Gemma[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]Oak[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Parsnip
Broccoli
[/TD]
[/TR]
[TR]
[TD]Company 4[/TD]
[TD]Bananas
Oranges
Kiwis[/TD]
[TD]Sofas[/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Keyboard
Desktop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Sam
Mike
Kat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Elm
Oak
Willow[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Scaffolding
Electricians[/TD]
[TD]Broccoli[/TD]
[/TR]
[TR]
[TD]Company 5[/TD]
[TD]Bananas
Apples[/TD]
[TD]Tables
Chairs[/TD]
[TD]Coffee[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Laptop
Mouse[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Mike
Sam
Steve[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Elm
Oak
Willow[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Builder
Brickie
Scaffolding[/TD]
[TD]Potato
Carrot[/TD]
[/TR]
[TR]
[TD]Company 6[/TD]
[TD]Melons
Bananas[/TD]
[TD]Cupboards[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Coffee
Cappucino[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Steve
Sam[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Elm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Carrot[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
KatRum said:
Hi All,

I received a really good response from here before and I am hoping someone can help me again. I browsed for an answer but it does not exist so far. I'll explain what I am trying to do and also what I have so far.

I have a large table with a lot of data. I have some dropdowns in B2:B9 which correspond with the data in columns DY:EF in the respective order. I would like the dropdowns to filter the data but only in the respoective field. People will only use one dropdown at a time so they don't have to interract with each other. I try to demonstrate below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]DY[/TD]
[TD]DZ[/TD]
[TD]EA[/TD]
[TD]EB[/TD]
[TD]EC[/TD]
[TD]ED[/TD]
[TD]EE[/TD]
[TD]EF[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lot 1[/TD]
[TD]Lot 2[/TD]
[TD]Lot 3[/TD]
[TD]Lot 4[/TD]
[TD]Lot 5[/TD]
[TD]Lot 6[/TD]
[TD]Lot 7[/TD]
[TD]Lot 8[/TD]
[/TR]
[TR]
[TD]Company 1[/TD]
[TD]Apples
Oranges
Kiwis[/TD]
[TD]Sofas
Armchairs
Tables
Chairs[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl63, width: 80"]Cups
Teapots[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 80"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 80"][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 80"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Desktop
Laptop
Mouse[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Kat
Sarah
Mike
Gemma[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Willow
Oak[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Scaffolding
Roofers[/TD]
[TD]Peas
Broccoli
Parsnip
Tomato
Potato[/TD]
[/TR]
[TR]
[TD]Company 2[/TD]
[TD]Apples
Kiwis[/TD]
[TD]Chairs
Tables[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Teapots
Coffee[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Mouse
Keyboard
Mobile[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Kat
Gemma[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Plumbers
Scaffolding
Electricians[/TD]
[TD]Potato
Parsnip
Tomato[/TD]
[/TR]
[TR]
[TD]Company 3[/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Tea
Coffee
Teapots[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]Mobile[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Sam
Gemma[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]Oak[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Parsnip
Broccoli[/TD]
[/TR]
[TR]
[TD]Company 4[/TD]
[TD]Bananas
Oranges
Kiwis[/TD]
[TD]Sofas[/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Keyboard
Desktop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Sam
Mike
Kat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Elm
Oak
Willow[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Scaffolding
Electricians[/TD]
[TD]Broccoli[/TD]
[/TR]
[TR]
[TD]Company 5[/TD]
[TD]Bananas
Apples[/TD]
[TD]Tables
Chairs[/TD]
[TD]Coffee[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Laptop
Mouse[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Mike
Sam
Steve[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Elm
Oak
Willow[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Builder
Brickie
Scaffolding[/TD]
[TD]Potato
Carrot[/TD]
[/TR]
[TR]
[TD]Company 6[/TD]
[TD]Melons
Bananas[/TD]
[TD]Cupboards[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Coffee
Cappucino[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Steve
Sam[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]Elm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Carrot[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


I currently have the following which only works for one dropdown:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub


Dim lastRow As Long
Dim myTable As Range


lastRow = Cells(Rows.Count, "EG").End(xlUp).Row
Set myTable = Range("A13:EG" & lastRow)


myTable.AutoFilter field:=129, Criteria1:="=*" & Range("B2").Value & "*"




End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Filter data by multiple independent drop-downs for multiple columns
Replies
6
Views
845
KatRum
K

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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