Originally Posted by
Jexcel
In column A I've got the following values:
test1
test2
test1
test1
test3
What I would like is for column B to show the unique values of column A:
test1
test2
test3
In column C I would like to count the number of times a value in B occurs in A. That would be:
3 (for 'test1')
1 (for 'test2')
1 (for 'test3')
I would use the next formula to count them:
=COUNT.IF(A1:A100;B1)
But I would first have to apply a filter to move the unique values of A to B. There isn't a formula available which would do this automatically upon typing values in A?
How about:
The first bit of the function identifies the second and subsequent occurrences of non unique values and shows a Null, and shows the count for the first instance.
HTH
Bookmarks