Friday, November 12, 2004

MS Excel: Finding unique entries in a column

Today, I came accross a situation where one column in MS Excel had account numbers with other column showing amount charged.Certain account numbers were charged for number of times during the period and we wanted to find out, how many accounts were charged during this period as number.This was part of bigger formula but finding the number of unique account was accomplished by creating a column and writing the following formula:
Assume Account Numbers are in column A and we created column F for this formula:

=IF(COUNTIF($A$1:A2,A2)>1,0,COUNTIF($A$1:A2,A2)) and copying it till the last account number row.

This gave value 1 for each unique account and 0 for repeatition.Just addition of this column provided me by number of unique accounts.

No comments: