I am trying to find the correct function to use, that will assign a number
value to the variety of different cell names in one column. Some of these
names contain letters and numbers. Please help!

Do you mean COUNTIF. Check it out in help and post back if that is not what
you need.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Chantal" wrote in message
...
I am trying to find the correct function to use, that will assign a number
value to the variety of different cell names in one column. Some of these
names contain letters and numbers. Please help!

please give an example of what you want

"Chantal" wrote:

I am trying to find the correct function to use, that will assign a number
value to the variety of different cell names in one column. Some of these
names contain letters and numbers. Please help!

"Chantal" wrote in message
...

I am trying to find the correct function to use, that will assign a number
value to the variety of different cell names in one column. Some of these
names contain letters and numbers. Please help!

If your data contains no blank cells, try this method (formula credit to
John Wallenbach):

Select all the cells in the column containing data and create a named-range
called "data".

Then select a column of cells elsewhere on your worksheet and name it
"result". Select enough cells to include all the unique cell values in your
data, or just select the same amount of cells as contained in your data, if
that is practical.

With the named-range "result" selected, insert the flollowing array formula
(Control+Shift+Enter):

=IF(ISERROR(INDEX(data,SMALL(IF(MATCH(data,data,0) =ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0) ,""),ROW(INDIRECT("1:"&ROWS(data)))))),"",INDEX(da ta,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&R OWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1 :"&ROWS(data))))))

This array formula will return all the unique values within the "data"
named-range in the order in which they were encountered within the list from
the top down.

Then to get a count of the unique values, put this formula in any cell:

=IF(ROWS(result)-COUNTBLANK(result)=ROWS(result),"Invalid Result - Increase
size of RESULT named-range",ROWS(result)-COUNTBLANK(result))

The formula will return the number of unique values within the data range.

Bob

Thank you, I will try this and let you know howit works out.

"Bob Davison" wrote:


"Chantal" wrote in message
...

I am trying to find the correct function to use, that will assign a number
value to the variety of different cell names in one column. Some of these
names contain letters and numbers. Please help!

If your data contains no blank cells, try this method (formula credit to
John Wallenbach):

Select all the cells in the column containing data and create a named-range
called "data".

Then select a column of cells elsewhere on your worksheet and name it
"result". Select enough cells to include all the unique cell values in your
data, or just select the same amount of cells as contained in your data, if
that is practical.

With the named-range "result" selected, insert the flollowing array formula
(Control+Shift+Enter):

=IF(ISERROR(INDEX(data,SMALL(IF(MATCH(data,data,0) =ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0) ,""),ROW(INDIRECT("1:"&ROWS(data)))))),"",INDEX(da ta,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&R OWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1 :"&ROWS(data))))))

This array formula will return all the unique values within the "data"
named-range in the order in which they were encountered within the list from
the top down.

Then to get a count of the unique values, put this formula in any cell:

=IF(ROWS(result)-COUNTBLANK(result)=ROWS(result),"Invalid Result - Increase
size of RESULT named-range",ROWS(result)-COUNTBLANK(result))

The formula will return the number of unique values within the data range.

Bob