- To the right of your data, copy the heading from the column where you want to find unique values.
- Select a cell in your data set.
- In Excel 97-2003, choose Data - Filter - Advanced Filter. In Excel 2007, choose the Advanced icon from the Sort & Filter group of the Data ribbon.
- Choose Copy to another Location
- In the Copy To box, specify the copy of your heading. In the Figure, this is cell D1
- Click the box for Unique Records Only
- Click OK
The COUNTIF function can count how many records above the current record match the current record. The trick to making this work is to use a single dollar sign in the reference. If you are entering a formula in C2 and you reference A$1:A1, this is saying, "Start from the absolute reference of A1 and go down to the record above the current record". When you copy this formula down, the first A$1 will stay the same. The second A1 will change. In Row 17, the formula in C2 will read: =COUNTIF(A$1:A16,A17)=0.
A pivot table is great at finding unique values. This is the fastest way in Excel 2000-2003.
- Select a cell in your data set.
- Choose Data - Pivot Table and Pivot Chart Report.
- Click Finish.
- In the Pivot Table Field List, click on the Customer Field. Click the Add To button.
Excel 2007 offers new methods for finding duplicates. Select the range of customers. From the Home ribbon, choose Conditional Formatting - Highlight Cells Rules - Duplicate Values and click OK.
- Click any field in the customer column. Click the AZ button in the Data ribbon.
- Find a cell that has the red highlighting. Right click the cell. Choose Sort - Put Selected Cell Color on Top.
This method is highly destructive! Make a copy of your dataset before you do this!
- Copy your range of data to a blank section of the worksheet
- Select a cell in your data set.
- From the Data ribbon, choose Remove Duplicates.
- The Remove Duplicates dialog will give you a list of columns. Choose the columns which should be considered. For example, if you needed to remove records where both the customer and invoice were identical, check the box for both fields. In this case, you are trying to get a unique list of customers, so choose only the Customer field.
- Click OK.
Link: http://www.mrexcel.com/tip138.shtml
No comments:
Post a Comment