Filter unique values in offset drop-list

SFUser

New Member
Joined
Feb 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have a data table of product attributes with the columns brand, subcategory and product. I have drop-list in a column of another sheet and next to that a subcategory drop-list. The method I usually use to make these dynamic is to use and OFFSET function. My problem is that the subcategory can appear in the dataset multiple times as there can be multiple products that have the same brand and subcategory; when the array is returned to the drop-list by the OFFSET function it therefore has multiples of the same subcategory. I could use the UNIQUE formula to create a list of distinct subcategories, but the filter changes on each row of my input sheet so that doesn't work. I could also create multiple subcategory lists and use the unique formula on each, but if a new subcategory is added that necessitates a new range be created.

So, my question is: how do I create a dynamic range with only unique values from within an OFFSET on each row of my input table. Essentially I'd like to filter the list as an array with each of the subcategories associated with a brand listed once only. But the kicker is I need to be able to do this on multiple rows with different brands entered. I hope that makes sense.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

SFUser

New Member
Joined
Feb 28, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
If anyone ever finds this I found a solution and thought I would post it here. I created a pivot table with the brand and subcategories, which then includes only one row of data for each subcategory. I was then able to use the offset method to populate the cascading drop-list from that.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,242
Messages
5,623,573
Members
415,981
Latest member
Baltwin

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
Top