0

I would like to count unique entries in a range between blanks. Please look at the attached pic for reference. The first set of data contains 3 unique entries so the highlighted box would 3. The second set of data has 3 unique entries and the highlighted box then would also display 3.

example

Community
  • 1
  • 1
Todd Nielsen
  • 13
  • 1
  • Sorry I cant see the image at the moment but maybe this question will assist. http://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel – Captain Grumpy Sep 13 '16 at 22:20

2 Answers2

0

@Todd Nielsen

Check the below link which has different solutions for it.

https://support.office.com/en-us/article/Count-unique-values-among-duplicates-7889942d-824e-4469-893c-191d1efde950#bmcount_the_number_of_unique_values__by

ravi sundriyal
  • 16
  • 1
0

Assuming you want to do this in VBA and the range containing the data sets is huge, the question is not as trivial as others which have replied to this post have made it out to be.

The following code requires that you set a reference to the Microsoft Scripting Runtime library. It inserts the number of unique values each time it finds an empty row in the search range (works best if there is only one empty row to separate the different data sets in the range):

Option Explicit

Sub uniquesEntriesInDataSets()

    Dim dict As Scripting.Dictionary
    Dim i As Long, startAt As Long, uCount As Long
    Dim cll As Range, searchRange As Range
    Dim val As Variant

    ' change sheetname and range to relevant parameters
    Set searchRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A21")

    Set dict = New Dictionary

    For Each cll In searchRange

        val = cll.Value

        If val = vbNullString Then
            If dict.Count > 0 Then 
                cll.Value = dict.Count
                dict.RemoveAll
            End if
        Else
            If Not dict.Exists(val) Then dict.Add Key:=val, Item:=i
        End If

    Next cll

End Sub
Miqi180
  • 1,670
  • 1
  • 18
  • 20
  • Not being sure how the OP is populating the original data, you may have to allow for the fact that the blank will be in column B, rather than A. (Because column A may no longer be blank if the code has been run once and only the data values in column A have since been changed.) So perhaps use `If IsEmpty(cll.Offset(0,1)) Then` rather than `If val = vbNullString Then`. – YowE3K Sep 14 '16 at 00:00
  • @YowE3K True, this code only gives the general idea on how to solve this problem for huge ranges in VBA, it does not take into account that the code may be run several times on the same range. If the OP wants the unique value counts in the adjacent column, then he should change `cll.Value = dict.Count` to `cll.Offset(0,1).Value = dict.Count`. Good catch. – Miqi180 Sep 14 '16 at 00:10