copy

Saturday, 31 August 2013



Quick Weekend Tips


About the Quick Weekend Tips (QWT) post series



QWT is a series of posts that are different from the “typical” posts that are being published in this blog. As the name implies the posts are published during the weekends (Saturday or Sunday) and their subject is quick/short tips about various applications (Excel, Power Point, AutoCAD etc.). These quick tips are accompanied with step by step instructions, as well as with a lot of pictures.



QWT 2: Get the unique values from an Excel list



Today we will see how to get the unique values from an Excel list/range using four different methods:
  • Removing duplicates.
  • Pivot table.
  • Advanced filter.
  • Array formula(s).
The sequence of the methods corresponds to the degree of difficulty, implying that the method of array formula is probably the most difficult to implement. All methods are analyzed below. In all examples we assume that the original list of employees is in the range B3:B28.

Original List



Removing duplicates



Step 1: Copy the original list to another location (for safety reasons - optional step).
Step 2:. Select a cell that is included in the list.
Step 3: Go to Data tab and press the Remove Duplicates icon.

Remove Duplicates Icon

Step 4: In the pop up window:
  1. Check the checkbox My data has headers (here has). 
  2. Check the desired Columns.
  3. Finally, press the OK button.
Remove Duplicates Window

Step 5: A message will inform you about how many duplicate values were found and deleted.

Duplicates Removed



Pivot table



Step 1: Select a cell that is included in the list.
Step 2: Go to Insert tab and press the Pivot Table icon.


 Pivot Table Icon

Step 3:  In the Create Pivot Table window:
  1. In the Choose data you want to analyze field select the input range, if is not already selected (here 'Pivot Table'!$B$3:$B$28).
  2. In the Choose where you want the Pivot Table report to be placed, select Existing Worksheet and specify the Location you want to store the Pivot Table (here 'Pivot Table'!$D$3).
  3. Press the OK button.
 Create Pivot Table Window

Step 4: In the Pivot Fields window select Employees and move it to the Rows field..

Pivot Table Fields Window



Advanced filter



Step 1: Select a cell that is included in the list.
Step 2: Go to Data tab and press the Advanced (filter) icon.

Advanced Filter Icon

Step 3: In the Advanced Filter window:
  1. Select the Copy to another location option.
  2. Specify your data in the List range (here 'Advanced Filter'!$B$3:$B$28). The heading (Employees) should be included in the selected List range.
  3. Check the Unique records only checkbox.
  4. Specify the output location at Copy to (here 'Advanced Filter'!$D$3).
  5. Press the OK button.
Advanced Filter Window

Step 4: A list with all the unique values will be created in the location you specified.



Array formula(s)



Step 1: Select an empty range with the same rows with your original list (here D4:D28).
Step 2: Type the following formula: =INDEX(B4:B28;SMALL(IF(MATCH(B4:B28;B4:B28;0)=ROW(INDIRECT("1:"&ROWS(B4:B28)));MATCH(B4:B28;B4:B28;0);"");ROW(INDIRECT("1:"&ROWS(B4:B28)))))
Step 3: When you finish typing, don't press ENTER, but CTRL + SHIFT + ENTER. Notice the brackets {} in the formula, implying an array formula.
Step 4: The empty range will be filled with the unique values of the initial range (here D4:D17), while #NUM! errors will appear in the rest of the cells (here D18:D28).

If you want to avoid the #NUM! errors in the range, use the IFERROR function:
Step 1: Select an empty range with the same rows with your original list (here F4:F28).
Step 2: Type the following formula: =IFERROR(INDEX(B4:B28;SMALL(IF(MATCH(B4:B28;B4:B28;0)=ROW(INDIRECT("1:"&ROWS(B4:B28)));MATCH(B4:B28;B4:B28;0);"");ROW(INDIRECT("1:"&ROWS(B4:B28)))));"")
Step 3: When you finish typing, don't press ENTER, but CTRL + SHIFT + ENTER. Notice the brackets {} in the formula, implying an array formula.
Step 4: The empty range will be filled with the unique values of the initial range (here F4:F17), while the rest of the cells will be empty (here F18:F28).

Get The Unique Values From An Excel List (Array Formulas)

In both formulas you can use defined names instead of ranges.



Sample file



Download

The file was created with Excel 2013, but it can be opened with Excel 2007 or newer.

Did you like this post? If yes, then share it with your friends. Thank you!



Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info