4 Methods to create a Unique list of records in Excel

in Excel For All9 months ago

4 different methods to create a Unique list of records in Excel are explained below.

  1. Pivot Table
  2. Array Formula
  3. Remove Duplicates
  4. Advanced Filter

To create a unique list of records for each item (Product, SalesRep and Region) from the data given in the table below.

Unique List of Records 1.PNG

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

Unique List of Records 2_Pivot Table.PNG

Now to create the unique of a particular field, for example, the field called Region, drag that field into the area for Rows.

Unique List of Records 3_Pivot Table.PNG

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.

Unique List of Records 4_Pivot Table.PNG

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


Unique List of Records 5_MultiCell Array Formula.PNG

Now, change the references in the formula to create the Unique list of records from the columns for Region and SalesRep

Unique List of Records 6_MultiCell Array Formula.PNG

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

Unique List of Records 7_Remove Duplicates.png

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.

Unique List of Records 8_Remove Duplicates.png

Excel displays the information about the number of Unique Values found and the duplicate values that were removed.

Unique List of Records 9_Remove Duplicates.PNG

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

Unique List of Records 10_Advanced Filter.png

We have 3 things to do in this Dialog for Advanced Filter,

  1. Select the Radio button against the label, Copy to another location to activate the input box against the label Copy to
  2. 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.
  3. Mark the checkbox against the label Unique Records Only and click OK

Unique List of Records 11_Advanced Filter.PNG

The unique list of items from the column for SalesRep is copied into Column F.

Unique List of Records 12_Advanced Filter.PNG


Congratulations @xlncad! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You got your First payout
You distributed more than 10 upvotes. Your next target is to reach 50 upvotes.

You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Vote for @Steemitboard as a witness to get one more award and increased upvotes!

Coin Marketplace

STEEM 0.17
TRX 0.03
JST 0.036
BTC 10838.95
ETH 364.87
USDT 1.00
SBD 0.98