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"
- Press with left mouse button on "Name Manager" button
- Press with left mouse button on "New.."
- Type UniqueStoreNumbers
- Select cell range =Data!$E$2:$E$27 in Refers to:
- Press with left mouse button on 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"
- Press with left mouse button on "Data validation.." button
- Go to tab "Settings"
- Select List in Allow: field
- Type in source: field: =UniqueStoreNumbers
- Press with left mouse button on 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.
- Press with right mouse button on on workbook in Project Explorer
- Press with mouse 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
Concatenate category
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 […]
This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]
Add cell values to a single cell with a condition, no VBA in this article.
Drop down lists category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
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?