How to filter and count unique values among duplicates
If you have a column of data that contains duplicates, then you can use the advanced filter option to identify and copy the unique values to a different location. You can then easily count those values using COUNTA function or the ROWS function.
Perform the following steps to achieve this goal:
1. First make sure that your column has a header row.
2. Select your data range (including the header row).
3. On the Data menu point to Filter, then click Advanced Filter. The Advanced Filter window will open.
4. Click Copy to another location.
5. The List range text box should have your data range. If not then click on it, go to your worksheet and select the range.
6. Click on the Copy to text box. Go to your sheet and click on a blank column (or just a single cell) where you want your unique vales to be copied.
7. Select the Unique records only check box and click OK.
8. To count the unique values just copied, use the COUNTA function or the ROWS function. E.g if the unique values are in the range E1:E6 then in a separate cell enter one of the following formulas:
1. “=COUNTA(E2:E6)”
2. “=ROWS(E2:E6)”
Applies to Excel 2003
