Use a drop down list to filter and concatenate unique distinct values
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that contains my data and I want to generate a drop down list based on the input from one column. Which I already have.
Once the user selects the value from the drop down, I want it to generate another unique list just in a column.
For example the sheet contains store numbers (one column) and employee names (a second column), which may be listed multiple times. I want to create the drop down for the store number and then creating a list of employee names (non-repeating) that isn't a drop down, just a standard list is say A1, A2, A3, A4, etc
Is that clear as mud?
Thanks.
Answer:
The animated image above shows drop down lists in column A and a UDF in column B, once a value has been selected in the dropdown list the UDF looks up the value in a data set and returns the corresponding values on the same rows as the matching numbers concatenated to one cell with a comma as a delimiting character.
The UDF returns a blank in column B if the cell in column A is blank.
What you will learn in this article
- Build an array formula that extracts unique distinct numbers sorted from low to high.
- How to use a named range.
- How to create a drop down list containing named range that points to numbers
- How to create a user defined function that extracts and concatenates values based on a drop down list value.
Customize worksheet "Data"
Column A contains store numbers and column B contains the corresponding name. The formula in cell E3 extracts all numbers from column A ignoring duplicates, sorted from low to high or ascending order.
Array formula in cell E3:
How to create an array formula
- Select cell E3
- Paste formula to cell
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keys.
The formula is now surrounded with curly brackets, they appear automatically if you did the above steps correctly. Do not enter the curly brackets yourself.
How to copy array formula
- Select cell E3
- Copy cell E3 (keyboard shortcut CTRL + c)
- Select cell range E4:E29 with you mouse.
- Paste (Keyboard shortcut: CTRL+v)
Explaining formula in cell E3
Step 1 - Ignore previously returned numbers above current cell.
The COUNTIF function lets you check which numbers have been returned above the current cell, the first argument contains an expanding cell reference that grows when you copy the cell and paste to cells below.
COUNTIF($E$1:E1, $A$2:$A$100)
becomes
COUNTIF("Unqiue store numbers",{22; 24; 9; 12; 5; 17; 22; 23; 0; 1; 14; 20; 16; 3; 25; 1; 12; 12; 20; 8; 7; 1; 2; 2; 7; 14; 4; 14; 22; 21; 8; 15; 17; 9; 12; 7; 19; 12; 7; 18; 4; 1; 14; 17; 7; 10; 16; 4; 2; 12; 19; 15; 15; 8; 3; 14; 12; 1; 5; 19; 16; 8; 15; 7; 18; 18; 2; 3; 14; 19; 9; 15; 11; 17; 23; 6; 12; 5; 5; 25; 9; 14; 25; 22; 22; 24; 2; 22; 21; 1; 20; 11; 16; 5; 5; 21; 0; 12; 1})
and returns
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
If a value is 0 (zero) in the array then the corresponding number has not yet been returned in above cells.
Step 2 - Check if value in array equals 0 (zero)
The equal sign allows you to check if a value in the array is equal to a given condition.
COUNTIF($E$1:E1, $A$2:$A$100)=0
becomes
{0; 0; 0; ... ; 0}=0
and returns
{TRUE; TRUE; TRUE; ... ; TRUE}
The array is shortened.
Step 3 - Return value from column A if value in array is TRUE
The IF function lets you return a given value if the first argument evaluates to TRUE and another value if the argument evaluates to FALSE. We are working with arrays so the position of each value is important since the corresponding value is returned.
IF(COUNTIF($E$1:E1, $A$2:$A$100)=0, $A$2:$A$100, "")
becomes
IF({TRUE; TRUE; TRUE; ... ; TRUE}, {22; 24; 9; ... ; 1}, "")
and returns
{22; 24; 9; ... ; 1}.
Step 4 - Return the smallest value in array
The SMALL function allows you to extract the k-th smallest number, however, in this case we always extract the smallest number since the COUNTIF function keeps track of previously returned numbers.
SMALL(IF(COUNTIF($E$1:E1, $A$2:$A$100)=0,$A$2:$A$100, ""), 1)
becomes
SMALL({22; 24; 9; ... ; 1}, 1)
and returns 0.
Create a named range
- Go to tab "Formulas"
- Click "Name Manager" button
- Click "New.."
- Type UniqueStoreNumbers
- Select cell range =Data!$E$2:$E$27 in Refers to:
- Click OK!
If you know you will be adding data later on then a dynamic named range is a better choice, it grows automatically when new data is entered.
Customize worksheet: Sheet1
Create drop down list
- Select cell A2
- Go to tab "Data"
- Click "Data validation.." button
- Go to tab "Settings"
- Select List in Allow: field
- Type in source: field: =UniqueStoreNumbers
- Click OK
Copy drop down list and paste to cells below
- Select cell A2.
- Copy cell (CTRL + c).
- Select cell range A3:A100.
- Paste values (CTRL + v).
Add vba code to module
- Copy VBA code below.
- Press Alt+F11 to open the Visual Basic Editor.
- Right click on workbook in Project Explorer
- Click on Insert and then Module.
- Paste VBA code to the code module.
- Return to excel.
How to enter the User defined function
Formula in cell B2:
Copy cell B2 and paste to cell range B3:B8.
Vba code
'Name User defined Function and input variables Function Concat_Unique(Lookup_Value As String, Lookup_Column As Range, Concat_column As Range) 'Dimension variables and declare data types Dim i As Single Dim Unique As New Collection Dim Value As Variant Dim result As String 'Iterate through lookup column For i = 1 To Lookup_Column.Cells.Rows.Count 'Check if lookup value is equal to value in lookup column If Lookup_Value = Lookup_Column.Cells(i).Value Then 'Check if character length is larger than 0 (zero) If Len(Concat_column.Cells(i)) > 0 Then 'Enable error handling On Error Resume Next 'Add value to collection. Returns an error if there is a duplicate in the collection Unique.Add Concat_column.Cells(i), CStr(Concat_column.Cells(i)) 'Disable error handling On Error GoTo 0 End If End If Next i 'Iterate through collection For Each Value In Unique 'Add values in collection to a string variable with a comma as a delimiting character result = result & Value & ", " 'Continue with next value in collection Next Value 'Check if variable result is empty If Len(result) = 0 Then 'Save a blank to variable result Concat_Unique = "" 'If not empty Else 'Remove two last characters (comma and a space character) and then return string to worksheet Concat_Unique = Left(result, Len(result) - 2) End If End Function
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]
Concatenate unique distinct values
The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to […]
Concatenate cell values based on a condition [No VBA]
Add cell values to a single cell with a condition, no VBA in this article.
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
Dependent drop-down lists in multiple rows
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
6 Responses to “Use a drop down list to filter and concatenate unique distinct values”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I am trying to create an estimating form that will calculate a total cost based of the values given. I have a drop down list, and i want it to produce a $ value for each item in a different cell. My goal is to create a form that will calculate total values.
Hi,
I had a question. I have a excel costing sheet. What it is is a pull down menu with options to build a trailer.
So one pull down menu is tires. Then i pull down and it has a bunch of different tire options. My question is how do i get the price to change when i select different tires?
There are 3 tires. Goodyear is 500.00 RM is 400 and DL is 800.00
so i go to the pull down menu and choose a tire then i need the correct price to go in the price box beside that. I have aformula to do this but it's so long and i think it can be alot easier.
Right now this is the formula:
=IF(A51="X",VLOOKUP(B51,Sheet1!$A$2:$B$12001,2,FALSE),"-")
BUT what it does is look up on one sheet who gets its information from another sheet who gets its information from another sheet. So there are all these sheets and im pretty sure i don't need them all. It's just making this spreadsheet very large and very slow!
Please help if you can
Aynsley,
Your formula seems to be fine.
yes, i know my formula is fine but i think it requires way too much work to do something that seems fairly simple.
it takes it's information from the options work sheet. which looks the price up on the price worksheet which looks it up on another work sheet to get the inforamtion.
it just seems like something could be cut somewhere.
first list AA, BB, CC
second list under AA - 11,22,33
under BB - 44, 55 etc
I have two column ... first column contains a list of AA,BB,CC
and in the next column it should return another list based on selection in the first column. for example if i select AA in the next column it should return another list with 11,22,33
is this posible???
I was trying to use drop down list with the help of data validation. But one problem is there, it does not take numbers or special characters. What could be the way out? as I do not have knowledge of vba will there be any other way out?