Getting the list of unique records (Method 1)

November 28th, 2005 by admin admin

Previously, we touched on the various methods to count the number of unique records listed in an excel worksheet. However, nothing was mentioned about producing the list of unique records using Excel. And I thought you might be interested to find out how. And here is one method you could use:

Assuming that the following product list is in column A starting in row 2.

What is a blog
Internet Marketing
Excel Formula
Adword Equaliser
Internet Marketing
Adword Equaliser
Excel Formula
Excel Formula

1) Sort the list so that all the same items are group together.
2) In cell A2, input the formula “=if (A2=A1,1,0)“.
3) This formula will check to see if the cell A2 is the same as A1. If they are the same, the number 1 will appear. If they are different, the number 0 will appear. Since we have grouped the same items together, the 1 will appear if item appears the second or third time in the list. For example:

Adword Equaliser 0
Adword Equaliser 1
Excel Formula 0
Excel Formula 1
Excel Formula 1
Internet Marketing 0
Internet Marketing 1
What is a blog 0

4) Activate the autofilter function and filter using the criteria “0”
5) Once the duplicates are hidden, use the Goto Special function to extract the unique list to another excel worksheet.

Mission completed.

About Goto Special function
Press the F5 function key and then click on the Special button. Select the option “Visible Cells only” (see diagram). Click “OK”. Only the visible cells are selected for now. Activate the copy function, go to another Excel worksheet, select a cell say A1 and activate the paste function.

Leave a Reply

You must be logged in to post a comment.