 |
 |
| About MrExcel |
| Consulting Services |
| Learn Excel Resources |
| Challenge of the Month |
| MrExcel Seminars |
| MrExcel Store |
| Podcast |
| Search |
| Media |
| Contact |
| Home |
 |
One of the annoying tasks in previous versions of Excel was finding a unique list of invoices, customers, or products in a data set. Today,
we’ll look at the various methods, including a new method in Excel 2007.
Method 1 - Advanced FilterLet's say you need a unique list of customers. Copy the Customer heading to a blank column in your worksheet. Select one cell in your data set. In Excel 97-2003, choose Data, Filter, Advanced. In Excel 2007, choose Data, Advanced Filter. In the dialog, choose Copy to a New Location. Choose Unique Records Only. In the Copy to box, click on the cell containing the copy of the word Customer.
Method 2 - COUNTIFUse a formula such as =COUNTIF(C$1:C2,C2)=1. Copy this formula to all rows. Change the formula to values using the Copy icon then open the Paste dropdown and choose Paste Values. Sort descending by the new column. All of the records with TRUE in the new column represent unique customer names.
Method 3 - Pivot Table MethodChoose a cell in the data set. In Excel 2007, use Insert - PivotTable. Click OK. Click Customer. In Excel 97-2003, use Data - Pivot Table and PivotChart Report. Click Finish. Double click the Customer field in the Pivot Table Field List.
Method 4 - New in Excel 2007 - Remove DuplicatesChoose a cell in your data set. From the Data tab, choose Remove Duplicates. Click Unselect All. Select the Customer field. Click OK. You will be left with just one record per customer. You should not run this command on your original data - make a copy first!
Check back after February 22, 2008 and you can view my segment here.
I really hope to give away 5 million copies of the book, so please send your co-workers and friends to
leobook.html.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.