4 different methods to create a Unique list of records in Excel are explained below.
- Pivot Table
- Array Formula
- Remove Duplicates
- Advanced Filter
To create a unique list of records for each item (Product, SalesRep and Region) from the data given in the table below.
Method 1 - Pivot Table
Select the Data > Go to Insert Tab > Click on Pivot Table > In the Dialog for Create Pivot Table, specify the location where you want to place the Pivot Table and Click OK
Now to create the unique of a particular field, for example, the field called Region, drag that field into the area for Rows.
Now that we have the unique list of Region, make two copies of that Pivot table and use the appropriate field names for the Uniques list of Product and SalesRep.
Method 2 - Array Formula
To create the Unique list of records from the data present in the range B3:B17,
Select the cells from F3 to F17
type in the multicell Array formula and press CTRL + SHIFT + ENTER
{=IFERROR(INDEX($B$3:$B$17,SMALL(IF(MATCH($B$3:$B$17,$B$3:$B$17,0)=ROW(INDIRECT("1:"&ROWS($B$3:$B$17))),MATCH($B$3:$B$17,$B$3:$B$17,0),""),ROW(INDIRECT("1:"&ROWS($B$3:$B$17))))),"")}
Now, change the references in the formula to create the Unique list of records from the columns for Region and SalesRep
Method 3 - Remove Duplicates
To create Unique list of records of data present in Column B, Copy and Paste column B into some other location, Select the copied column, Click on Remove Duplicates in the Data tab
Remove Duplicates dialog is of great use when we are analyzing data present in more than one column. In this, we don’t need to change anything other than clicking the OK button.
Excel displays the information about the number of Unique Values found and the duplicate values that were removed.
Method 4 - Advanced Filter
To create a unique list of records using Advanced Filter, select the cells containing records > Click on Advanced Filter in the Data tab
We have 3 things to do in this Dialog for Advanced Filter,
- Select the Radio button against the label, Copy to another location to activate the input box against the label Copy to
- Using the input box against the Label Copy to, specify the location where we want the records to placed. Here I will select the cell F2.
- Mark the checkbox against the label Unique Records Only and click OK
The unique list of items from the column for SalesRep is copied into Column F.
