4 Methods to create a Unique list of records in Excel

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

Comments 7

Thanks for sharing the link, Robot. That's my answer in Quora.
I have written more than 400 answers in Quora, 99% of them are related to Excel.



07.01.2020 13:37

hi @xlncad, I see cheetah came to visit. If you are duplicating content, you need to add proper citations and links to the original publication. You should also state that if it is your own or someone else content.

As the steem rewards pool is limited, we have some 'protections' to try to ensure the rewards pool is given to content that adds value to the blockchain. Duplicate content tends not to be seen as value-adding. The message from Cheetah is to allow curators to asses the value it brings to the blockchain when giving or removing rewards by upvotes/downvotes.

Does that make sense?

maybe @cheetah would like to step in here and give more advice?

07.01.2020 22:30

hi @paulag, it is my own answer/article.
From your reply, I understand that the already published Article/Answer should be shared along with links.

08.01.2020 04:19

Yes, if the content was not published first to the steem blockchain you need to share details within the post. And don't be surprised if these type of posts are not rewarded via the general steem blockchain.
I have still to define and layout some sort of rewards structure within the community that will be a separate thing to the general steem rewards. however, a factor in this will be how much value something adds to the community in general.

You are really active on quora. where do you find all the time?

08.01.2020 11:29

Thank you. That explains a lot.
Frankly speaking, I see Quora as a notepad for my future articles. If you have gone through my answers you may have noticed that most of them are all comprehensive.
At the same Quora serves as an idea bank for my YouTube videos.

08.01.2020 13:48