Tabulation the number of different names in one column
 

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!

Tabulation the number of different names in one column
 

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!

Tabulation the number of different names in one column
 

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!

Tabulation the number of different names in one column
 

"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

Tabulation the number of different names in one column
 

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