Displaying unique items in an Excel list

At times, you may wish to display a list of unique items from an Excel list and place the unique items into cells.  There are a number of way to do this - three of the easiest techniques, two manual techniques and one automatic formula technique are shown below.

Looking for Microsoft Excel training? Click here for our courses.

  

Manual technique - Remove Duplicates feature in Excel 2007 onwards

If you are using version 2007 or onwards of Excel, you can simply highlight the list, click the Data tab then click Remove Duplicates. Tick the columns in which each row of data should be entirely unique then click OK.

Very easy for users of Excel 2007, Excel 2010 and onwards.

  

Manual technique - Advanced Filter feature in Excel 2003 and earlier

The Remove Duplicates feature does not exist in this simple form in Excel 2003 and earlier, however there is a similar feature to this in the Advanced Filter.

If you are using version 2003 or earlier of Excel, highlight the list, click the Data drop-down menu then click Filter, then Advanced Filter.  Now tick Unique Records Only then click OK.

Note options on the panel to copy the filtered list to another location and a field to specify the location.  This is not necessary to do, but you may find it useful.

To redisplay the full list, click the Data drop-down menu then click Filter then Show All.

  

Automatic technique - using formulas

While these techniques are great for manually removing the duplicate items in a list, you may wish this to happen automatically with formulas so the result automatically updates as changes are made to the source data. This can be much more difficult, however one of the most simple solutions is described below.

This simple formula can extract and display unique items from a list. If the data begins in cell A2, you would place this formula in another cell such as B2:  

=IF(COUNTIF($B$2:B2,B2)=1,B2,"")

Autofill this formula down (or copy paste) so the formula data table is the same size as the source data table.

Too easy!

If you wish to have the blank cells removed so you have a solid list of the unique items, then this will take a lot more work.  I highly recommend the CPearson webpage on this subject here:

http://www.cpearson.com/excel/NoBlanks.aspx

  

  

Nicholas Jankovic

T7 Training Systems

  

Monday, 9th January 2012

 

HomeInformation and pricingCoursesMake a bookingBlogContact us
© 2011 T7 Training Systems

Magicdust Responsive Website Design

HomeInformation and pricingCoursesMake a bookingBlogContact us