Count Unique Values in a Cell - and the Entire Range that Cell Resides In

[Philometis]

I have a worksheet that has a column of cells but each cell has one to several field names. I find lots of guidance on counting unique values in a range of cells BUT not within cell and ranges.

The values in the cells are alphanumeric field names such as COLCOD, A15OB, ACCTNO etc.

The goal if not clear (the worksheet that this column is in has roughly 400 rows) is to count the unique instances of fields in the entire column BUT considering one cell in that column may have several fields as noted above. More specifically one cell might have:

COLCOD
A15OB
ACCTNO

In the above instance I need to return a unique count of 3. But as noted, those same fields could and will be referenced in other cells in the same column, and if those were the only fields, then the unique count of the column of cells would also be 3.

Currently, the fields are not separated by commas and though they happen to lie vertically in the column, that is simply due to the column width, so I may I need to use commas to help delineate a change in fields.

Greatly appreciate the help lads. I have found there to be plenty of brilliant people on this board and I know I can't be the first to have uncovered this need.

Finally, I would prefer the solution be contained within Excel functions since if I introduce VBA I believe it will impair my ability to share the sheet and results with several people that the worksheet gets shared with.

Thanks!

Philo

 

[hiker95]

Re: Count Unique Values in a Cell - and the Entire Range that Cell Resides In - SOLVED

Philometis,

Thanks for the Private Message.

We have a winner.

Sample raw data (not all 406 rows are shown for brevity):

Excel 2007
	A
1
2	L.S.D.W. Source Field
3	CALREP STATUS ACCTNO IN35A1
4	CALREP STATUS LNCUDI
5
6
7
8
9
10
405	CALREP STATUS ACCTNO IN35A1
406	CALREP STATUS ACCTNO IN35A1
407
408
Sheet1

After the new macro:

Excel 2007
	A
1
2	L.S.D.W. Source Field
3	CALREP STATUS ACCTNO IN35A1
4	CALREP STATUS LNCUDI
5
6
7
8
9
10
405	CALREP STATUS ACCTNO IN35A1
406	CALREP STATUS ACCTNO IN35A1
407	73
408
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub GetUniqueCount_V3()
' hiker95, 09/09/2014, ME803634
Dim c As Range, n As Long, s, i As Long, lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In Range("A3:A" & lr)
    If c <> "" Then
      If InStr(c, vbLf) Then
        s = Split(c, vbLf)
        For i = LBound(s) To UBound(s)
          If Not .exists(s(i)) Then
            .Add s(i), 1
            n = n + 1
          End If
        Next i
      Else
        If Not .exists(c.Value) Then
          .Add c, 1
          n = n + 1
        End If
      End If
    End If
  Next c
End With
With Cells(lr + 1, "A")
  .Value = n
  .Font.Bold = True
  .HorizontalAlignment = xlCenter
End With
Columns("A:A").AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniqueCount_V3 macro.

 

[Philometis]

Re: Count Unique Values in a Cell - and the Entire Range that Cell Resides In - SOLVED

Hiker95

You are he master, Hiker95. I'll shall properly digest this after at least one cup of coffee tomorrow. Your patience and experience is sincerely appreciated.

Philo

 

[hiker95]

Re: Count Unique Values in a Cell - and the Entire Range that Cell Resides In - SOLVED

Philometis,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.