VideoPhoto

14 May, 2021

Counting unique cell entries in a worksheet

Sometimes you may want to be sure that your large Excel dataset contains unique entries only, i. e. none of the entries appears more than once. How would you check it?

There are some array formulas and advanced filtering methods available for such occasion, but the most convenient way is probably using UDF (User defined function) I'm presenting here:

Function Count1time(cRng As Range) As Variant
'Counts each cell with a value in the selected range
'Excludes any repeats!
'example of usage: Count1time(A1:C100)
Dim cVal As Variant
Dim uniq As New Collection
Application.Volatile
On Error Resume Next
For Each cVal In cRng
    uniq.Add cVal, CStr(cVal)
Next
Count1time = "Found " & uniq.Count & " unique entries"
End Function

To use it - in your workbook - open the Visual Basic Editor (VBE) by pressing ALT+F11 shortcut, right-click within Project Explorer, select Insert > Module (if not inserted there yet). Copy and paste the function code in the Module window.

Switch back to your Excel worksheet and test the function on any selected set of data. If the function counting result is equal to the number of cell entries within the tested range, it confirms that all data in your set are de facto unique. If not - some values are entered more than once.

 

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.