Unique Values in Dynamic Down List

**philchi** · 07-23-2019, 07:48 PM

Hello,

I'd like to automatically populate a drop-down list with the unique values contained in a table's column using the value of another column. So if my table looked like this:

REGION	OFFER
North	Learning Tools
North	Learning Tools
North	New Offers

I'd want the drop-down list to only show "Learning Tools" and "New Offers" when "North" is selected in a different cell (the attached table shows other regions). It would be great if the drop-down list is sorted - but not mandatory.

Is this possible without macros/VBA?

Thanks

**MarvinP** · 07-23-2019, 08:36 PM

Hi philchi,

See the attached that does what I think you want. I did need to do a macro to accomplish your problem.

Dynamic Dropdown using Advanced Filter Unique.xlsm

**philchi** · 07-23-2019, 08:56 PM

Thanks for responding MarvinP. Unfortunately, Excel Macros are disabled by Windows Group Policy in our organization so I cannot use this solution. Also cannot use VBA because it is not supported on SharePoint online.

Maybe there's a two step process - one array to get all of the Offers based on the Region and a separate function in the Data Validation List field that only retrieves the the unique values from that array.

Possible?

**MarvinP** · 07-23-2019, 11:55 PM

Hi philchi,

I used a simple Advanced filter to do your problem. Then I automated it to change based on a Validation cell. You could learn Advanced Filters and do the problem manually. You simply change the North, South, etc and click through the Advanced Filter dialog. Hope this helps.

If you had a newer version of Excel like 2016 there are new functions called Dynamic Array Functions that can do your problem. What version of Excel do you have?
https://www.excelcampus.com/function...-spill-ranges/ if you have updated 2016 version of Excel.

**philchi** · 07-24-2019, 01:35 PM

MarvinP,

I'm using Office 365 (a much newer version than 2016) and my understanding is Dynamic Arrays are not in General Availability - only limited members in the Excel Insiders program have access to these features. But yes, if the functionality was generally available, I could use the new =UNIQUE and =SORT functions to meet my needs.

You (or anyone else) have any ideas on how to accomplish this with arrays?

**JeteMc** · 07-28-2019, 02:00 PM

This may seem like a round about way to get this done, however...
1. Populate columns L:O for the four regions using: =COUNTIFS(OffersTable[[Offer]:[Offer]],$K9,OffersTable[[Region]:[Region]],L$8)
2. Populate lists of contents in columns G:J using: =IFERROR(INDEX($K$9:$K$22,AGGREGATE(15,6,(ROW($K$9:$K$22)-ROW($K$8))/(L$9:L$22>0),ROWS($A$1:$A1))),"")
3. Name (see name manager) the lists of contents using the following as the refers to: =Solutions!$I$9:$I$22
4. Use the following as the source of the data validation: =OFFSET(INDIRECT(G3),0,0,SUMPRODUCT(--(INDIRECT(G3)<>"")),1)
Let us know if you have any questions.

**philchi** · 07-28-2019, 04:36 PM

The example above partially works though it wasn't clear to me how to incorporate in the dropdown. Perhaps I wasn't clear in my question so I've updated the original post's attachment (and posted here as well).

I've figured out how to get the unique values in the drop-down list, however it requires three separate functions each piggy-backing off of the other.

Based on the clarified question/expected result and my sample - is there a better way of doing this without VBA/Macros (perhaps cut the 3 formula down into a single formula)???

Thanks

**philchi** · 08-02-2019, 11:39 AM

In case the separate question confused things - anyone have any suggestions on how to make this process more effcient - cut the 3 formulas down to 2 (maybe even 1) formula?

**Bo_Ry** · 08-02-2019, 12:03 PM

Please try at
F17
=IFERROR(INDEX(OffersTable3[Offer],SMALL(IF(FREQUENCY(IF(OffersTable3[Region]=$F$16,MATCH(OffersTable3[Offer],OffersTable3[Offer],)),ROW(OffersTable3)-ROW(A$1)),ROW(OffersTable3)-ROW(A$1)),ROWS(F$17:F17))),"")
Press Ctrl+Shift+Enter and drag down

Data Validation list
=OFFSET($F$17,,,COUNTIF($F$17:$F$27,"?*"))

**philchi** · 08-02-2019, 12:59 PM

Originally Posted by Bo_Ry

Please try at
F17
=IFERROR(INDEX(OffersTable3[Offer],SMALL(IF(FREQUENCY(IF(OffersTable3[Region]=$F$16,MATCH(OffersTable3[Offer],OffersTable3[Offer],)),ROW(OffersTable3)-ROW(A$1)),ROW(OffersTable3)-ROW(A$1)),ROWS(F$17:F17))),"")
Press Ctrl+Shift+Enter and drag down

Data Validation list
=OFFSET($F$17,,,COUNTIF($F$17:$F$27,"?*"))

Bo_Ry - thanks for responding but for some reason it only works when "East" is selected. If North, South or West is entered in F16, there are blanks in F17 on and some of the Offers entries are missing. There aren't any blanks in the table so I'm curious how those are being returned.

**Bo_Ry** · 08-02-2019, 01:03 PM

Please press Ctrl+Shift+Enter and drag down again

**philchi** · 08-02-2019, 01:05 PM

Originally Posted by Bo_Ry

Please press Ctrl+Shift+Enter and drag down again

Yep, that fixed it - Thanks!!!

Unique Values in Dynamic Down List

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Re: Unique Values in Dynamic Down List

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] dynamic and dependent drop down list with unique values

[SOLVED] Create a list of unique values from several columns in a dynamic table.

[SOLVED] Get list of unique values from dynamic range

dynamic list of unique values

[SOLVED] Dynamic Unique List from multiple dynamic columns

[SOLVED] List unique values from multiple dynamic lists

Create unique list from dynamic list with dupes

Bookmarks

Bookmarks

Posting Permissions