4 Methods to create a Unique list of records in Excel

[xlncad]

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.

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,

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

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

Comments

[cheetah]

Hi! I am a robot. I just upvoted you! I found similar content that readers might be interested in:
https://www.quora.com/How-do-I-retrieve-unique-values-from-a-list-in-Excel

[xlncad]

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

https://www.quora.com/profile/Ajay-Anand-202

[paulag]

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?

[xlncad]

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.

[paulag]

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?

[xlncad]

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.

[steemitboard]

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!