I think that unless you want to do all these investigations manually, you
want to ignore the find/replace command. It is an excellent tool for finding
something in a spreadsheet that you lost, but as far as using it for day to
day operations, it is a non-starter, IMHO.
Read through the following, extracted from the Help System, and see if it
doesn't give you some insight into the solution of your problem. (BTW, this
is much easier to read in the help system.)
Bill
Count unique values among duplicates
Show All
Hide All
Let's say you want to find out how many unique values exist in a range that
contains duplicate values. For example, if a column contains:
The values 5, 6, 7, and 6, then the result is three unique values—5 , 6 and 7.
The values "Buchanan", "Dodsworth", "Dodsworth", "Dodsworth", then the
result is two unique values—"Buchanan" and "Dodsworth".
There are several ways to count unique values among duplicates.
What do you want to do?
Count the number of unique values by using a filter
Count the number of unique values by using functions
--------------------------------------------------------------------------------
Count the number of unique values by using a filter
You can use the Advanced Filter to extract the unique values from a column
of data and paste them to a new location. Then you can use the ROWS function
to count the number of items in the new range.
Ensure that the first row in the column has a column header.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Copy to another location.
If the range that you are counting is not already selected, delete any
information in the List range box and then click the column (or select the
range) that contains your data.
In the Copy to box, delete any information in the box or click in the box,
and then click a blank column where you want to copy the unique values.
Select the Unique records only check box, and click OK.
The unique values from the selected range are copied to the new column.
In the blank cell below the last cell in the range, enter the ROWS function.
Use the range of unique values that you just copied as the argument. For
example, if the range of unique values is B1:B45, then enter:
=ROWS(B1:B45)
Top of Page
--------------------------------------------------------------------------------
Count the number of unique values by using functions
Use the IF, SUM< FREQUENCY, MATCH, and LEN functions to do this task:
Assign a value of 1 to each true condition by using the IF function.
Add the total by using the SUM function.
Count the number of unique values by using the FREQUENCY function. The
FREQUENCY function ignores text and zero values. For the first occurrence of
a specific value, this function returns a number equal to the number of
occurrences of that value. For each occurrence of that same value after the
first, this function returns a zero.
Return the position of a text value in a range by using the MATCH function.
This value returned is then used as an argument to the FREQUENCY function so
that the corresponding text values can be evaluated.
Find blank cells by using the LEN function. Blank cells have a length of 0.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return
the results, press CTRL+` (grave accent), or on the Tools menu, point to
Formula Auditing, and then click Formula Auditing Mode.
1
2
3
4
5
6
7
8
9
10
A B
Data Data
986 Buchanan
Dodsworth 563
67 789
235
Buchanan Dodsworth
689 789
Dodsworth 143
56 237
67 235
Formula Description (Result)
=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1)) Count the number of unique number
values in cells A2:A10, but do not count blank cells or text values (4)
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
Count the number of unique text and number values in cells B2:B10 (which must
not contain blank cells) (7)
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""),
IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1)) Count the number of
unique text and number values in cells A2:A10 , but do not count blank cells
or text values (6)
Notes
The formulas in this example must be entered as array formulas (array
formula: A formula that performs multiple calculations on one or more sets of
values, and then returns either a single result or multiple results. Array
formulas are enclosed between braces { } and are entered by pressing
CTRL+SHIFT+ENTER.). Select each cell that contains a formula, press F2, and
then press CTRL+SHIFT+ENTER.
To see a function evaluated step by step, select the cell containing the
formula. Then, on the Tools menu, point to Formula Auditing and click
Evaluate Formula.
Function details
FREQUENCY
MATCH
LEN, LENB
SUM
IF
Top of Page
See Also
Buy Microsoft Office 2007
Count how often a value occurs
Free trial of the 2007 Microsoft Office system
Use formulas to edit, correct, and proofread text
