How to extract unique values from a range – 2 Ways

Often we need to extract unique values from a range of data. Although to do that there is already a built in Remove Duplicates feature in Excel. But here are 2 more ways to do it

1. A Formula to Extract Unique Values

Here is the formula =IFERROR(INDEX($A$3:$A$22,MATCH(0,COUNTIF($B$2:B2,$A$3:$A$22),0)),””)

Note a few things in the formula

1. The COUNTIF formula uses an expandable range which starts from $B$2 (i.e. one cell above, next to the corresponding column where the data starts – A3)
2. Since we used absolute cell referencing for $B$2 (beginning of the range) and no referencing for B2 (end of the range) the range will expand as you move your formula down
3. Since this an array formula, you’ll have to confirm the formula by pressing CTRL+SHIFT+ENTER

2. VBA Code to extract unique Values

Sub Extract_Unique()

Set Rng = Range('A2:A22')

Set destrng = Range('D2')

 Rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Rng, _
 CopyToRange:=destrng, unique:=True
 
End Sub

The red portion of the code is editable. Here are a few points to help you customize this code for your own data

1. A2:A22 – Write the range of your own data.
2. D2 – Customize this to the location where you want to paste the unique values

Here are a few steps that will help you paste the code in the VBA Window

1. Copy the above Code and customize it as per your data
2. Open the excel workbook where you want to extract unique values
3. Press the shortcut Alt + F11 to open the Visual Basic Window
4. In the Insert Menu, click on Module or use the shortcut Alt i m to add a Module. Module is the place where the code is written.
5. In the blank module paste the code and close the Visual Basic Editor
6. Then use the shortcut Alt + F8 to open the Macro Box. You would have the list of all the macros here, you would see our Macro as 'Extract_Unique'
7. Run it.
8. Instantly you’ll see the unique values are extracted

Download the Excel file from Down Below (With both methods)

Solutions to Everyday Problems

1. Create Dependent Drop Downs
2. Different Date Problems and their Solutions
3. Navigating Between Sheets Quickly
4. Consolidate Data from Multiple Sheets
5. Create an Index of Sheet Names