Extracting a Unique list
using Excel Advanced Filter to quickly extract unique records from a data set (or in other words remove duplicates).
- Select the entire data set (including the headers).
- Go Data tab –> Sort & Filter –> Advanced. (You can also use the keyboard shortcut – Alt + A + Q). This will open the Advanced Filter dialog box.
In the Advanced Filter dialog box, use the following details:
- Action: Select the ‘Copy to another location’ option. This will allow you to specify the location where you can get the list of unique records.
- List Range: Make sure it refers to the dataset from which you want to find unique records. Also, make sure headers in the data set are included.
- Criteria Range: Leave this empty.
- Copy To: Specify the cell address where you want to get the list of unique records.
- Copy Unique Records Only: Check this option
- Click Ok
This will instantly give you a list of all the unique records.
Using Criteria in Excel Advanced Filter
using Excel Advanced Filter to filter the records based on the specified criteria:
- The first step when using Excel Advanced Filter with complex criteria is to specify the criteria. To do this, copy the headers and paste it somewhere in the worksheet.
- Specify the criteria for which you want to filter the data.
- Select the entire data set (including the headers).
- Go Data tab –> Sort & Filter –> Advanced. This will open the Advanced Filter dialog box
- In the Advanced Filter dialog box, use the following details:
- Action: Select the ‘Copy to another location’ option. This will allow you to specify the location where you can get the list of unique records.
- List Range: Make sure it refers to the dataset from which you want to find unique records. Also, make sure headers in the data set are included.
- Criteria Range: Specify the criteria we constructed in the steps above.
- Copy To: Specify the cell address where you want to get the list of unique records.
- Copy Unique Records Only: Check this option.
- Click OK.