Filtering Challenge

MrMel76

New Member
Joined
Apr 14, 2019
Messages
6

<tbody>
[TD="class: xl68"]Business Unit[/TD]
[TD="class: xl68"]Cluster[/TD]
[TD="class: xl68"]Country[/TD]
[TD="class: xl68, width: 112"]Category[/TD]
[TD="class: xl68, width: 116"]Subcategory[/TD]
[TD="class: xl68, width: 49"]Segment[/TD]
[TD="class: xl68, width: 56"]Market[/TD]
[TD="class: xl68, width: 152"]Sector[/TD]
[TD="class: xl68, width: 54"]SubSector[/TD]
[TD="class: xl68, width: 46"]Brand[/TD]
[TD="class: xl68, width: 81"]Productnumber[/TD]
[TD="class: xl68, width: 37"]Sales[/TD]
[TD="class: xl68, width: 55"]Turnover[/TD]
[TD="class: xl68, width: 31"]Cost[/TD]
[TD="class: xl68, width: 49"]Profit[/TD]
[TD="class: xl68, width: 96"]Turnover per piece[/TD]
[TD="class: xl68, width: 74"]Cost per piece[/TD]
[TD="class: xl68, width: 79"]Profit per piece[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]UK & Ireland[/TD]
[TD="class: xl66"]Ireland[/TD]
[TD="class: xl66"]Fastfood & Drinks[/TD]
[TD="class: xl66"]Refreshments[/TD]
[TD="class: xl66"]Burgers[/TD]
[TD="class: xl66"]Events[/TD]
[TD="class: xl66"]Festivals[/TD]
[TD="class: xl66"]Outdoor[/TD]
[TD="class: xl66"]OYT[/TD]
[TD="class: xl66, align: right"]123[/TD]
[TD="class: xl66, align: right"]100[/TD]
[TD="class: xl66, align: right"]2000[/TD]
[TD="class: xl66, align: right"]750[/TD]
[TD="class: xl66, align: right"]1250[/TD]
[TD="class: xl67, align: right"]20,00[/TD]
[TD="class: xl67, align: right"]7,50[/TD]
[TD="class: xl67, align: right"]12,50[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]UK & Ireland[/TD]
[TD="class: xl66"]Ireland[/TD]
[TD="class: xl66"]Fastfood & Drinks[/TD]
[TD="class: xl66"]Fast Food[/TD]
[TD="class: xl66"]Soda[/TD]
[TD="class: xl66"]Events[/TD]
[TD="class: xl66"]Festivals[/TD]
[TD="class: xl66"]Outdoor[/TD]
[TD="class: xl66"]TB[/TD]
[TD="class: xl66, align: right"]456[/TD]
[TD="class: xl66, align: right"]175[/TD]
[TD="class: xl66, align: right"]1500[/TD]
[TD="class: xl66, align: right"]1000[/TD]
[TD="class: xl66, align: right"]500[/TD]
[TD="class: xl67, align: right"]8,57[/TD]
[TD="class: xl67, align: right"]5,71[/TD]
[TD="class: xl67, align: right"]2,86[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]UK & Ireland[/TD]
[TD="class: xl66"]Ireland[/TD]
[TD="class: xl66"]Fastfood & Drinks[/TD]
[TD="class: xl66"]Refreshments[/TD]
[TD="class: xl66"]Soda[/TD]
[TD="class: xl66"]Events[/TD]
[TD="class: xl66"]Festivals[/TD]
[TD="class: xl66"]Outdoor[/TD]
[TD="class: xl66"]RTD[/TD]
[TD="class: xl66, align: right"]789[/TD]
[TD="class: xl66, align: right"]225[/TD]
[TD="class: xl66, align: right"]1750[/TD]
[TD="class: xl66, align: right"]1200[/TD]
[TD="class: xl66, align: right"]550[/TD]
[TD="class: xl67, align: right"]7,78[/TD]
[TD="class: xl67, align: right"]5,33[/TD]
[TD="class: xl67, align: right"]2,44[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]UK & Ireland[/TD]
[TD="class: xl66"]United Kingdom[/TD]
[TD="class: xl66"]Fastfood & Drinks[/TD]
[TD="class: xl66"]Refreshments[/TD]
[TD="class: xl66"]Soda[/TD]
[TD="class: xl66"]Events[/TD]
[TD="class: xl66"]Festivals[/TD]
[TD="class: xl66"]Outdoor[/TD]
[TD="class: xl66"]OYT[/TD]
[TD="class: xl66, align: right"]123[/TD]
[TD="class: xl66, align: right"]300[/TD]
[TD="class: xl66, align: right"]500[/TD]
[TD="class: xl66, align: right"]250[/TD]
[TD="class: xl66, align: right"]250[/TD]
[TD="class: xl67, align: right"]1,67[/TD]
[TD="class: xl67, align: right"]0,83[/TD]
[TD="class: xl67, align: right"]0,83[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]UK & Ireland[/TD]
[TD="class: xl66"]United Kingdom[/TD]
[TD="class: xl66"]Fastfood & Drinks[/TD]
[TD="class: xl66"]Fast Food[/TD]
[TD="class: xl66"]Burgers[/TD]
[TD="class: xl66"]Events[/TD]
[TD="class: xl66"]Festivals[/TD]
[TD="class: xl66"]Outdoor[/TD]
[TD="class: xl66"]TB[/TD]
[TD="class: xl66, align: right"]456[/TD]
[TD="class: xl66, align: right"]550[/TD]
[TD="class: xl66, align: right"]375[/TD]
[TD="class: xl66, align: right"]135[/TD]
[TD="class: xl66, align: right"]240[/TD]
[TD="class: xl67, align: right"]0,68[/TD]
[TD="class: xl67, align: right"]0,25[/TD]
[TD="class: xl67, align: right"]0,44[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]UK & Ireland[/TD]
[TD="class: xl66"]United Kingdom[/TD]
[TD="class: xl66"]Fastfood & Drinks[/TD]
[TD="class: xl66"]Refreshments[/TD]
[TD="class: xl66"]Soda[/TD]
[TD="class: xl66"]Events[/TD]
[TD="class: xl66"]Festivals[/TD]
[TD="class: xl66"]Outdoor[/TD]
[TD="class: xl66"]RTD[/TD]
[TD="class: xl66, align: right"]789[/TD]
[TD="class: xl66, align: right"]600,5[/TD]
[TD="class: xl66, align: right"]1255[/TD]
[TD="class: xl66, align: right"]700[/TD]
[TD="class: xl66, align: right"]555[/TD]
[TD="class: xl67, align: right"]2,09[/TD]
[TD="class: xl67, align: right"]1,17[/TD]
[TD="class: xl67, align: right"]0,92[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]CEE[/TD]
[TD="class: xl66"]Cyprus[/TD]
[TD="class: xl66"]Variety Sauces[/TD]
[TD="class: xl66"]Refreshments[/TD]
[TD="class: xl66"]Soda[/TD]
[TD="class: xl66"]Events[/TD]
[TD="class: xl66"]Festivals[/TD]
[TD="class: xl66"]Outdoor[/TD]
[TD="class: xl66"]OYT[/TD]
[TD="class: xl66, align: right"]147[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]100[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl66, align: right"]50[/TD]
[TD="class: xl67, align: right"]2,00[/TD]
[TD="class: xl67, align: right"]1,00[/TD]
[TD="class: xl67, align: right"]1,00[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]CEE[/TD]
[TD="class: xl66"]Cyprus[/TD]
[TD="class: xl66"]Variety Sauces[/TD]
[TD="class: xl66"]Fast Food[/TD]
[TD="class: xl66"]Burgers[/TD]
[TD="class: xl66"]Events[/TD]
[TD="class: xl66"]Festivals[/TD]
[TD="class: xl66"]Outdoor[/TD]
[TD="class: xl66"]TB[/TD]
[TD="class: xl66, align: right"]258[/TD]
[TD="class: xl66, align: right"]25[/TD]
[TD="class: xl66, align: right"]75[/TD]
[TD="class: xl66, align: right"]45[/TD]
[TD="class: xl66, align: right"]30[/TD]
[TD="class: xl67, align: right"]3,00[/TD]
[TD="class: xl67, align: right"]1,80[/TD]
[TD="class: xl67, align: right"]1,20[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]CEE[/TD]
[TD="class: xl66"]Cyprus[/TD]
[TD="class: xl66"]Variety Sauces[/TD]
[TD="class: xl66"]Refreshments[/TD]
[TD="class: xl66"]Soda[/TD]
[TD="class: xl66"]Events[/TD]
[TD="class: xl66"]Festivals[/TD]
[TD="class: xl66"]Outdoor[/TD]
[TD="class: xl66"]RTD[/TD]
[TD="class: xl66, align: right"]369[/TD]
[TD="class: xl66, align: right"]35[/TD]
[TD="class: xl66, align: right"]120[/TD]
[TD="class: xl66, align: right"]35[/TD]
[TD="class: xl66, align: right"]85[/TD]
[TD="class: xl67, align: right"]3,43[/TD]
[TD="class: xl67, align: right"]1,00[/TD]
[TD="class: xl67, align: right"]2,43[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]BNL[/TD]
[TD="class: xl66"]Belgium[/TD]
[TD="class: xl66"]Variety Sauces[/TD]
[TD="class: xl66"]Sauces[/TD]
[TD="class: xl66"]Sweet[/TD]
[TD="class: xl66"]Home[/TD]
[TD="class: xl66"]Summer[/TD]
[TD="class: xl66"]Mild[/TD]
[TD="class: xl66"]OYT[/TD]
[TD="class: xl66, align: right"]741[/TD]
[TD="class: xl66, align: right"]500[/TD]
[TD="class: xl66, align: right"]8000[/TD]
[TD="class: xl66, align: right"]4000[/TD]
[TD="class: xl66, align: right"]4000[/TD]
[TD="class: xl67, align: right"]16,00[/TD]
[TD="class: xl67, align: right"]8,00[/TD]
[TD="class: xl67, align: right"]8,00[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]BNL[/TD]
[TD="class: xl66"]Belgium[/TD]
[TD="class: xl66"]Variety Sauces[/TD]
[TD="class: xl66"]Sauces[/TD]
[TD="class: xl66"]Sweet[/TD]
[TD="class: xl66"]Home[/TD]
[TD="class: xl66"]Summer[/TD]
[TD="class: xl66"]Hot[/TD]
[TD="class: xl66"]TB[/TD]
[TD="class: xl66, align: right"]852[/TD]
[TD="class: xl66, align: right"]800[/TD]
[TD="class: xl66, align: right"]12000[/TD]
[TD="class: xl66, align: right"]5500[/TD]
[TD="class: xl66, align: right"]6500[/TD]
[TD="class: xl67, align: right"]15,00[/TD]
[TD="class: xl67, align: right"]6,88[/TD]
[TD="class: xl67, align: right"]8,13[/TD]

[TD="class: xl66"]Europe[/TD]
[TD="class: xl66"]BNL[/TD]
[TD="class: xl66"]Belgium[/TD]
[TD="class: xl66"]Variety Sauces[/TD]
[TD="class: xl66"]Sauces[/TD]
[TD="class: xl66"]Sour[/TD]
[TD="class: xl66"]Home[/TD]
[TD="class: xl66"]Summer[/TD]
[TD="class: xl66"]X-treme[/TD]
[TD="class: xl66"]RTD[/TD]
[TD="class: xl66, align: right"]963[/TD]
[TD="class: xl66, align: right"]250[/TD]
[TD="class: xl66, align: right"]3500[/TD]
[TD="class: xl66, align: right"]2750[/TD]
[TD="class: xl66, align: right"]750[/TD]
[TD="class: xl67, align: right"]14,00[/TD]
[TD="class: xl67, align: right"]11,00[/TD]
[TD="class: xl67, align: right"]3,00[/TD]

</tbody>


[TABLE="width: 731"]
<tbody>[TR]
[TD]Selection[/TD]
[TD]Dropdown[/TD]
[TD][/TD]
[TD]Selection[/TD]
[TD]Dropdown[/TD]
[TD][/TD]
[TD]Selection[/TD]
[TD]Dropdown[/TD]
[/TR]
[TR]
[TD]Business Unit[/TD]
[TD]Europe[/TD]
[TD][/TD]
[TD]Business Unit[/TD]
[TD]Europe[/TD]
[TD][/TD]
[TD]Business Unit[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]Cluster[/TD]
[TD]UK & Ireland[/TD]
[TD][/TD]
[TD]Cluster[/TD]
[TD]UK & Ireland/CEE[/TD]
[TD][/TD]
[TD]Cluster[/TD]
[TD]UK & Ireland/CEE[/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]United Kingdom[/TD]
[TD][/TD]
[TD]Country[/TD]
[TD]United Kingdom/Cyprus[/TD]
[TD][/TD]
[TD]Country[/TD]
[TD]Ireland/United Kingdom/Cyprus[/TD]
[/TR]
[TR]
[TD]Category[/TD]
[TD]Fastfood & Drinks[/TD]
[TD]or[/TD]
[TD]Category[/TD]
[TD]Fastfood & Drinks[/TD]
[TD]or[/TD]
[TD]Category[/TD]
[TD]Fastfood & Drinks[/TD]
[/TR]
[TR]
[TD]Subcategory[/TD]
[TD]Refreshments[/TD]
[TD][/TD]
[TD]Subcategory[/TD]
[TD]Refreshments[/TD]
[TD][/TD]
[TD]Subcategory[/TD]
[TD]Refreshments[/TD]
[/TR]
[TR]
[TD]Segment[/TD]
[TD]Soda[/TD]
[TD][/TD]
[TD]Segment[/TD]
[TD]Soda[/TD]
[TD][/TD]
[TD]Segment[/TD]
[TD]Soda[/TD]
[/TR]
[TR]
[TD]Market[/TD]
[TD]Events[/TD]
[TD][/TD]
[TD]Market[/TD]
[TD]Events[/TD]
[TD][/TD]
[TD]Market[/TD]
[TD]Events[/TD]
[/TR]
[TR]
[TD]Sector[/TD]
[TD]Festivals[/TD]
[TD][/TD]
[TD]Sector[/TD]
[TD]Festivals[/TD]
[TD][/TD]
[TD]Sector[/TD]
[TD]Festivals[/TD]
[/TR]
[TR]
[TD]SubSector[/TD]
[TD]Outdoor[/TD]
[TD][/TD]
[TD]SubSector[/TD]
[TD]Outdoor[/TD]
[TD][/TD]
[TD]SubSector[/TD]
[TD]Outdoor[/TD]
[/TR]
[TR]
[TD]Brand[/TD]
[TD]RTD[/TD]
[TD][/TD]
[TD]Brand[/TD]
[TD]RTD[/TD]
[TD][/TD]
[TD]Brand[/TD]
[TD]RTD[/TD]
[/TR]
</tbody>[/TABLE]


So what do I want to do?

Based on my non-unique brand name filter my (financial) data ( productnumber to Profit per piece) based on various header criteria
As an example for my criteria please see the various selection tables. The selection will be done by a dropdown list. I have a formula that makes the row data in the table unique (till brand).That will be the source data for my dropdown

The result of the filtering will become the base for a vlookup.

The path that I want to follow preferably is BU -> Cluster->Country-> Category->Subcategory->Segment->Market->Sector->Subsector. The users for this will be people with minimal excel skill. The will just make a selection and get the output. So I want to make this as simple as possible.

I can do this with slicers BUT I can’t make a dropdown list out of my slicer criteria or I don’t know how.

What is the best way to do this?

Thanks in advance.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi James,

Thanks for your help but I think for what I want to do using slicers will be the most practical way. Too bad I can't make a slicer selection linked to a dropdown list from a different tab. I don't know if its possible in Excel at the moment
 
Upvote 0
Hi James been off the grid for a while and just saw your reply. Much appreciated I will take a look at this..but very much appreciated from my side..you have been a big help
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,435
Members
452,326
Latest member
johnshaji

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