Now, how can I have my values also sorted alphabetically? (I do not want to use the filter on my master table)
Should I use a COUNTIFS instead of COUNTIF?
For a column range which the values are changing regularly, and you always need to get all the unique values from the range no matter how it changed. How to make a dynamical list of unique values? This article will show you how to deal with it.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
As below screenshot shown, you need to dynamically extract a list of unique values from range B2:B9. Please try the following array formula.
1. Select a blank cell such as D2, enter formula =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") (B2:B9 is the column data which you want to extract the unique values, D1 is the above cell where your formula is located) into the Formula Bar, and then press the Ctrl + Shift + Enter keys simultaneously. See screenshot:
2. Keep selecting cell D2, then drag the Fill Handle down to get all unique values from the specified range.
Now all unique values in column range B2:B9 are extracted. When values in this range changed, the unique value list will be dynamically changed immediately.
You can also extract a list of unique values dynamically from a column range with the following VBA code.
1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Module window.
VBA code: Extract a list of unique values from a range
Sub CreateUniqueList() Dim xRng As Range Dim xLastRow As Long Dim xLastRow2 As Long Dim I As Integer On Error Resume Next Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8) If xRng Is Nothing Then Exit Sub On Error Resume Next xRng.Copy Range("D2") xLastRow = xRng.Rows.Count + 1 ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row For I = 1 To xLastRow2 If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete End If Next End Sub
Note: In the code, D2 is the cell you will locate the unique value list. You can change it as you need.
3. Go back to the worksheet, click Insert > Shapes > Rectangle. See screenshot:
4. Draw a rectangle in your worksheet, then enter some words you need to display on it. Then right click it and select Assign Macro from the right-clicking menu. In the Assign Macro dialog box, select the CreateUniqueList in the Macro name box, and then click the OK button. See screenshot:
5. Now click on the rectangle button, a Kutools for Excel dialog box pops up, please select the range contains unique values you need to extract, and then click the OK button.
From now on, you can repeat the above step 5 to update the unique value list automatically.